Wekelijkse Excel rapportage opstellen uit Exact Online

Voor het rapporteren van een wekelijks dashboard zou ik graag grootboeksaldo’s uit de database willen halen per wekelijkse periode, of tussen twee datums.

De huidige formules onder ‘Balansen’ in de module ‘Bouwblokken’ zijn zeer geschikt voor de maandelijkse rapportages. Echter, ik zou dat graag op wekelijkse basis kunnen doen.

Een formule waarbij ik een datum van- tot en met zou in kunnen vullen zou de oplossing zijn.

Wie kan mij helpen?

Bestaande Excel formules voor Exact Online balansen en saldi

De formules onder “Balansen” / “Saldi” (laatste versies) zijn uitsluitend te gebruiken per financiële periode vanwege performance. Exact Online houdt namelijk doorlopend de saldi bij voor een combinatie van dimensies:

  • administratie
  • boekjaar
  • boekperiode
  • grootboekrekening
  • eventueel kostenplaats
  • eventueel kostendrager

Deze (sterk ingedikte) informatie is technisch terug te vinden in tabellen zoals BalanceLinesPerPeriod of ReportingBalance.

Een overzicht van deze formules is te vinden op De meest populaire Exact Online Excel add-in formules.

Andere periodes voor tussentijdse rapportages

Om de saldi van mutaties op te vragen voor alles wat buiten de genoemde dimensies ligt, zoals bijvoorbeeld datum vanaf/datum tm, is het nodig om de relevante boekingen in die periode op te halen en op te tellen. Dat kan op verschillende manieren.

Wat handig is, hangt af van de beschikbare vaardigheden, het aantal administraties en de omvang van de individuele administraties. Een administratie met elke dag 50.000 boekingen (die zijn er) vereist een andere aanpak dan een administratie met in totaal in 3 jaar 50.000 boekingen. Ter illustratie: het gemiddeld aantal boekingen per Exact Online administratie is 15.000.

Met een Excel formule in de Excel add-in

Voorkennis: Excel, Exact Online, werken met formules

Het Exact Online saldo over een periode tussen twee willekeurig datums kan real-time met een Excel formule opgevraagd worden met de Excel add-in. De Excel formule voor dit Exact saldo is I_EOL_TXN_AMOUNT_DC. Hierin kunnen ook meerdere grootboeknummers opgegeven worden zoals “8*,65*” voor alle grootboekrekeningen die beginnen met een ‘8’ of ‘65’.

Voor meer uitleg zie Nieuwe Excel consolidatiemogelijkheden voor Exact Online bedrijven en grootboekrekeningen.

Met Excel Data Analyse

Voorkennis: Excel, Exact Online, werken met draaitabellen

Voor omgevingen met niet te gek veel boekingen (paar honderdduizend hoogstens) kan een download gemaakt worden vanuit de Bouwblokken → Exact Online → Boekstukregels. Via een paar stappen worden alle boekingen opgehaald en geplakt in het Excel-sheet.

Omdat de resultaten ook een naam hebben (zie “Name Manager” in het lint) kun je hier vlot een draaitabel van maken.

Met Excel blokken

Voorkennis: Excel, Exact Online, SQL, modelbouw

Invantive Control kent ook blokken. Hiermee kun je complexe calculaties bouwen, maar ook flexibele downloads. De basis is altijd een SQL-query binnen de modeleditor van de Excel add-in, in dit geval bijvoorbeeld:

select *
from   TransactionLinesIncremental
where  Date >= $X{Sheet1!A1}
and    Date <= $X{Sheet1!A2}

Merk op dat ook de SQL-statements rekening houden met de ingestelde administratie. Als er meerdere administraties gekozen zijn, dan wordt alle Exact Online data uit die administraties tegelijk opgehaald.

De begin- en einddatum worden uit de cellen A1 en A2 gehaald.

Er is een 30-tal voorbeeldrapporten beschikbaar waar ook een dergelijk rapport tussen zit. Indien gewenst sturen we die rapporten graag op. Laat hiervoor een antwoord achter.

Het gebruik van Excel-blokken is redelijk complex, maar ook enterprise-niveau. De cashflowprognose voor Exact Online maakt hier bijvoorbeeld gebruik van.

Een voorbeeld voor het maken staat in de video:

Met Get My Report

Voorkennis: Excel, Exact Online

In elk abonnement waar Invantive Control for Excel zit, zit ook Get My Report. In Get My Report zit het rapport Boekingsregels waarmee alle boekingen opgehaald kunnen worden. Alle individuele boekingsregels zitten in de resulterende Excel-sheet. Omdat de resultaten ook een naam hebben (zie “Name Manager” in het lint) kun je hier vlot een draaitabel van maken. Video’s over het gebruik van draaitabellen zijn op YouTube vlot te vinden.

Voor meer uitleg zie Transaction Lines - Get My Report.

Met Query Tool

Voorkennis: Excel, Exact Online, SQL

Vanuit Invantive Control kan ook Query Tool gestart worden. Hiermee kun je queries maken zoals:

select *
from   TransactionLinesIncremental
where  Date >= to_char('20200101', 'YYYYMMDD')
and    Date <= to_char('20201231', 'YYYYMMDD')

local export results as "c:\temp\dump.xlsx" format xlsx include headers

De query kan gedraaid worden via “Execute All” of F5. Ook queries met honderden stappen en complexe bedrijfslogica zoals facturatie en synchronisatie kunnen zo draaien.

Met Power Query

Voorkennis: Excel, Exact Online, Power Query

In elk Invantive abonnement met Invantive Control zit ook de Power Query/Power BI connector op Invantive Cloud. Hiermee kun je snel en vlot de data downloaden in Power Query (Power Pivot).

Maak hiervoor een Exact Online database aan zoals beschreven op Verbind Power Query met Exact Online, 180 dagen gratis (of bekijk de video’s). Download daarna de tabel TransactionLinesIncremental. Gebruik indien nodig een filterstap in Power Query.

Met Access OData

Voorkennis: Excel, Exact Online

In elk Invantive abonnement met Invantive Control zit ook de Power Query/Power BI connector op Invantive Cloud. Hiermee kun je snel en vlot de data downloaden op https://access-odata.com

Maak hiervoor een Exact Online database aan zoals beschreven op Verbind Power Query met Exact Online, 180 dagen gratis (of bekijk de video’s).

Ga naar https://access-odata.com

Meld je aan.

Selecteer en download de tabel TransactionLinesIncremental.

Ook hier kun je werken met draaitabellen. Video’s over het gebruik van draaitabellen zijn op YouTube vlot te vinden.

Programmatisch

Voorkennis: Excel, Exact Online, SQL, PSQL

Programmeurs kunnen ook zelf precies de structuur sturen van de Invantive SQL-queries op de boekingen. Op bijvoorbeeld Invantive Cloud en andere Invantive SQL-producten kunnen Excel-werkboeken via programmacode samengesteld en gevuld worden. Dit is vooral handig als het gaat om complexe rapportages die herleidbaar en betrouwbaar telkens opnieuw moeten draaien.

Voor meer uitleg zie Excel download uit Exact Online in eigen formaat met een cloud app.

Deze vraag is automatisch gesloten na tenminste 2 weken inactiviteit nadat een mogelijk passend antwoord is gegeven. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.