Inhoud 1 Plugin functions 2 Setting up you books. 2.1 Create a Alias 2.2 Setup parameters 3 Functions 3.1 Account totals 3.2 Account totals by range 3.3 Debtor openitemamounts 3.4 Advanced 4 Plugins in Excel

Plugin functions

This plugin enables you to update your existing and new spreadsheets directly, quick and efficient with data from osFinancials and TurboCash without importing, copy/past or using other report generators. This tool allows you to easily generate interim reports, annual reports, consolidation sheets and budgetsheets.

Setting up you books.

Create a Alias

First you assign a so-called alias per accounting entity through a new designed menubar called osFinancials (OSF-Zoek/Alias).

Setup parameters

Next please look for the worksheet ‘Instellingen’. You can define an unlimited amount of alias to be used in your reporting in this worksheet. You can define the start en ending period for each alias in column D and column E. In column F you can define the reporting year; value 0 is the current year and value 1 is the previous year.

Functions

Account totals

=GetAccTotal(Alias;AccountNumber;VanPeriode;TotPeriode;VorigJaar)

This function returns the ending balance for a general ledger accountnumber for the values as defined in the worksheet ‘Instellingen’ or for different values to be entered here separately. De variabel ‘AccountNumber’ can be assigned by adding a celreference.(See cel A4 below) You can design a consolidation spreadsheet by accountnumber level in this way. You can however only apply this when you have an identical general ledger for each accounting entity (=alias). An alternative is to consolidate on a financial category level; see next function.

Account totals by range

=GetTotalsByAccountRange1(Alias;VanRekening:T/MRekeningGroep;VanPeriode;TotPeriode;VorigJaar) =GetTotalsByAccountRange2(Alias;VanRekening:T/MRekeningGroep;VanPeriode;TotPeriode;VorigJaar) =GetTotalsByAccountRange3(Alias;VanRekening:T/MRekeningGroep;VanPeriode;TotPeriode;VorigJaar) =GetTotalsByAccountRange4(Alias;VanRekening:T/MRekeningGroep;VanPeriode;TotPeriode;VorigJaar)

These functions return the total balance for a range of general ledger accountnumbers defined by the first number, first two, first three or first four numbers of each accountnumber. The column ‘VanRekening’ is the value of the range beginning and the column ‘T?MRekenningGroep’ is the value of the range ending. The columns ‘VanPeriode’ and ‘TotPeriode’ are for the values as defined in the worksheet ‘Instellingen’ or for different values to be entered here separately.

In the example below cel C2 shows a total of all accountnumbers starting with the number ‘0’ and in cel C3 shows a total of all accountnumbers starting with the number ‘1’. In cel C4 you can see how the range 0 to 1 totals all accountnumbers starting with the number ‘0’ until ‘1’.Cel C5 shows how to group totals on the first two digits in the accountnumbers. Please note that the function has changed from =GetTotalsByAccountRange1 into =GetTotalsByAccountRange2

Debtor openitemamounts

=GetOpenItemsByDebtorRange(Alias;VanRekening;TotRekening;TotPeriode;VorigJaar) This function returns the total balance for a range of creditors or debtors until a specified reporting period.See cel B9. The values for cels B2 en B3 can be found by using the ‘OSF-zoek’ option in the osFinancials menubar.

Advanced

=GetSql(Alias;B6&B7&B8&B9) This function enables experienced users the possibility to design a SQL query in cels B6, B7, and B8 themselves. Cel B9 is only for ending the SQL query with a correct quotation mark. Other SQL examples can be seen in cels C2, D2 and E2.

Plugins in Excel

In Excel, plugins can be disabled. To see if Excel is blocking the osFinancials plugin, please go to Help->Info->Disabled items If osFinancials is in the list you can unblock it there