Hoe consolideer ik SnelStart in Excel-kolommenbalans met saldi uit Power Query?

Met Invantive Cloud is het eenvoudig mogelijk om meerdere administraties te consolideren in Excel tot een kolommenbalans volgens eigen opmaak. Dit artikel legt u uit hoe u een geconsolideerde kolommenbalans voor meerdere SnelStart-administraties maakt in Excel met behulp van Invantive Cloud, Snelstart en Power Query in Excel.

Alle stappen uit dit artikel kunt u uitvoeren met de gratis SnelStart-connector van Invantive (het zogenaamde “Free Plan”) (zie Beheers gratis gegevens op SnelStart).

Zodra u de onderstaande formules gemaakt heeft, hoeft u alleen nog op “Alles vernieuwen” te drukken om de kolommenbalans volledig te actualiseren met real-time financiële gegevens uit SnelStart.

Deze stappen kunt u ook volgen als u maar 1 SnelStart-administratie wilt rapporteren.

Het samenstellen van de geconsolideerde kolommenbalans bestaat uit drie stappen:

  • voorbereidingen om gegevens via Power Query te kunnen ophalen;
  • gegevens in Power Query laden in Excel;
  • formules maken.

De saldi komen uit de view KolommenBalansPerPeriode.

Voorbereidingen

Indien er meerdere administraties geconsolideerd moeten worden, dan volgt u de instructies in Data uit meerdere SnelStart-administraties real-time consolideren en legt u bijvoorbeeld voor 3 administraties een view vast bij de database startup SQL zoals:

create or replace force view KolommenBalansPerPeriodeAll
as
select 'NOORD' Administratie
,      t.*
from   KolommenBalansPerPeriode@snt_wmynoord t
union all
select 'ZUID' Administratie
,      t.*
from   KolommenBalansPerPeriode@snt_wmyzuid t
union all
select 'BEHEER' Administratie
,      t.*
from   KolommenBalansPerPeriode@snt_holding t

Gegevens in Power Query laden in Excel

Vervolgens start u Microsoft Excel en gaat u naar het lint “Gegevens” en kiest u een OData-feed:

Vervolgens kopieert u de URL die achter “Bridge Online URL” staat in Invantive Cloud bij de database naar Excel:

Na 30 tot 120 seconden zijn alle saldi van alle jaren geladen in Excel’s Power Query:

image

Plaats de gegevens in een Excel-tabel, daar te kiezen voor “Laden naar…”:

image

en dan “Tabel”:

image

De gegevens verschijnen in het werkboek. Plaats de cursor in de tabel en ga naar het lint “Tabelontwerp”. Verander de naam in iets korters zoals “Saldi”:

De saldi van alle administraties zijn nu beschikbaar om te verwerken met formules.

Wilt u de saldi bijwerken? Kies dan in het lint “Gegevens” voor “Alles vernieuwen”:

image

Formules maken

In deze laatste stap gaat u de saldi volgens uw eigen wensen verwerken tot bijvoorbeeld een geconsolideerde kolommenbalans. Hiervoor gebruiken we de Excel-formules SUMIFS (in de Nederlandse Excel-versies: SOMMEN.ALS).

De eerste parameter van SUMIFS is de kolom in de salditabel met de saldo’s. Die gaat Excel optellen. De waarde is “Saldi[Saldo]”.

Gedurende het invoeren van de tekst toont Excel ook automatisch de mogelijke velden:

image

De formule SUMIFS heeft daarnaast een veelvoud telkens twee parameters om een dwarsdoorsnede te maken.

Wilt u bijvoorbeeld zoals in onderstaande afbeelding voor rij 6 de saldi van alle administraties van de grootboekrekening 1300 in C6 optellen per periode, dan gebruikt u:

=SUMIFS(Saldi[Saldo]; Saldi[PERIODE]; F$5; Saldi[GrootboekRekeningNummer]; $C6)

Of in de Nederlandse versie:

=SOMMEN.ALS(Saldi[Saldo]; Saldi[PERIODE]; F$5; Saldi[GrootboekRekeningNummer]; $C6)

Wilt u echter het saldo per administratie weten zoals in rijen 7 t/m 10, dan gebruikt u:

=SOMMEN.ALS(Saldi[Saldo]; Saldi[PERIODE]; F$5; Saldi[GrootboekRekeningNummer]; $C7; Saldi[Administratie]; $D7)

U hoeft niet perse met een kolommenstructuur te werken. U kunt ook rustig deze formules naar eigen inzicht verspreiden in het Excel-werkboek.

Zo maakt u in drie stappen eenvoudig een geconsolideerde kolommenbalans in Excel met real-time data uit SnelStart, waarbij u enkel nog op “Alles vernieuwen” hoeft te drukken om de rapportage volledig te actualiseren.