Excel financials [in-depth guide]
Guide contents: Download | How to use the Excel Financials interface | To calculate a worksheet or workbook now | Troubleshooting | Resources
The Excel Financial interface lets you access your financial data directly from an Excel spreadsheet. Using the Financial interface requires you to learn only four new functions, which do the following:
- Get the total amount for transactions within a time period
- Get the budget amount for a time period
- Get the net income as of a specified date
- Get the retained earnings as of a specified time period
Use of Excel financials
- The Excel Financial files are only available for the Windows desktop version of Microsoft Excel versions 2000 and later. NOTE: The online Excel 365 version does not allow Macros. Please use the desktop version.
- The download links below provide a ZIP file that contains several pre-made templates for you to customize, including a blank layout that has the connection Macros already built in.
- You must enable Macros in order for the spreadsheet to be able to connect to your Workamajig account. Microsoft has turned this into a 2-step process:
1. From the File explorer > right-click on the file you wish to use > Properties > Security warning > check the UNBLOCK
2. Open the file > you will see a Security warning > click Enable content, to enable the macros - Summary GL accounts: Summary/roll-up accounts do not have any values or transactions associated with them, it is for display/printing only. You MUST use the detail account numbers to pull data into the worksheet.
- To force a refresh of the data and/or make the login screen popup, press F9 on your keyboard or click on and out of a cell that contains the get amount function. This will then retrieve the GL balances and populate the spreadsheet.
- If editing the provided fields, different versions of Excel may handle the fields differently.
For those using Mac or are not using Excel or using Excel 365, we are currently testing a way to add custom calculations to the Corporate P&L screen, or you may want to try Google financials (Google sets a 1000 record call limit). Please contact your Workamajig account manager for further details of these alternatives.
Download - versions
Normal login version - User ID + Password
Uses your web address, username & password.
ExcelFinancials.zip
This version requires the following:
1) Workamajig URL: (YourApp.workamajig.com). Do not add /platinum to the end
2) Your user ID and password to access Workamajig.
The user must have the security right to access GL transactions for the reports to work.
Tokenized version - User access token + company access token
Uses your web address, user token & company token.
ExcelFinancials_20201207.zip
This version requires the following:
1) Workamajig URL: (https://YourApp.workamajig.com/platinum)
2) User API token and company API token detailed below
The user token will identify "your" user ID in the system.
The user must have the security right to access GL transaction for the reports to work
Copy user token
To copy/paste your user token, click on your name in the upper right. Then, click on Create new token, or click the existing token to then get the options to generate a New user token or Copy to clipboard.
Copy company token
To copy/paste the company token, go to System setup > Connections > API. Here, you can generate a New company token or Copy to clipboard.
How to use the Excel Financials interface
As noted above, there are four functions in the Excel Financial interface. Here they are again, with their function names:
- GetAmount - Get the total amount of journals for a time period
- GetBudget - Get the budget amount for the selected time period
- GetNetIncome - Get the net income as of a specified date
- GetRetainedEarnings - Get the retained earnings as of a specified time period
NOTE: If editing the default code found in a worksheet cell, different versions of Excel may not be compatible. Below is just one example of this difference between Excel versions:
Ex. =@GetAmount($A58,"c",$E$3,$E$4,,D$11)
The above works for older versions of Excel, but for version 2019 and above, the cell should be
=@GetAmount($A58,"c",$E$3,$E$4,"",D$11)>> double quotes are added between commas to designate a 'null' or empty field
Insert function
You can use Excel's built-in feature called Insert Function to help with the formatting and validation of each cell's formula.
[Insert Image]
- Using the Insert Function feature from a selected cell will bring up a custom dialog box of the available arguments.
- This will then create a number of commas to separate the values and also add the quotes around named values.
GetAmount()
The purpose of this function is to obtain from the Workamajig financial engine the total of all journal entries for a given time period.
The format of GetAmount is as follows:
GetAmount (AccountRange,balance type,StartDate,EndDate,ClassId,Department,Office,ClientId,ProjectNumber,CompanyName,CashBasis) where:
- AccountRange = a single account number, a group of account numbers separated by commas, or a range of account numbers designated with a colon. The list must be enclosed by double-quotes. Examples: "100-01,120-00:120-09". This is a required field in this function.
- BalanceType = "C" for Credit, or "D" for Debit. This tells the financial engine that the resulting balance is expected to be a Credit balance or a Debit balance. Example: A cash account would be a "D", even if the balance in the account were below zero. This is a required field in this function.
- StartDate = This instructs the financial engine to total those entries starting at this date. This is a required field in this function
- EndDate = This instructs the financial engine to total those entries ending at this date. This is a required field in this function.
- ClassId = This instructs the financial engine to pull only those transactions with the specified ClassId. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of class.
- Department = This instructs the financial engine to pull only those transactions with the specified Department. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of the Department.
- Office = This instructs the financial engine to pull only those transactions with the specified Office. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Office.
- ClientId = This instructs the financial engine to pull only those transactions with the specified Client Id. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Client id.
- ProjectNumber = This instructs the financial engine to pull only those transactions with the specified Project Number. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Project Number.
- CompanyName = This instructs the financial engine to pull only those transactions with the specified GL Company Name. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of GL Company Name.
- CashBasis = If you want the values to be run as Cash Basis accounting, then enter 1 as the value.
GetBudget()
The purpose of this function is to obtain from the Workamajig financial engine the total of all journal entries for a given time period and a given Budget Name.
NOTE: Starting month & ending month are based on your budget and your Fiscal year starting month. So if your Fiscal year begins in November, on your budget, this will be seen as the first month, so in Excel Financials, you will enter StartingMonth=1 to capture the November budget and 12 to capture the September budget.
The format of GetBudget is as follows:
GetBudget (AccountRange, BudgetName, StartingMonth, EndingMonth, ClassID, Department, Office, ClientID, CompanyName, CashBasis)
where:
- AccountRange = a single account number, a group of account numbers separated by commas, or a range of account numbers designated with a colon. The list must be enclosed by double-quotes. Examples: "100-01,120-00:120-09". This is a required field in this function.
- BudgetName = This instructs the financial engine to pull only those transactions with the specified Budget Name.
- StartingMonth = This instructs the financial engine to total those entries starting with this month. The field entry equals 1, 2, 3... which corresponds to Jan, Feb, Mar. This is a required field in this function.
- EndingMonth = This instructs the financial engine to total those entries ending at this month. The field entry equals 1, 2, 3... which corresponds to Jan, Feb, Mar. This is a required field in this function.
- NOTE: For Starting and Ending Month, if you want to see the budget total for the January through March, you would enter Starting Month = 1 and Ending Month = 3. If you want just to see January, you would enter StartingMonth=1 and EndingMonth=1
- ClassId = This instructs the financial engine to pull only those transactions with the specified ClassId. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of class.
- Department = This instructs the financial engine to pull only those transactions with the specified Department. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of the Department.
- Office = This instructs the financial engine to pull only those transactions with the specified Office. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Office.
- ClientId = This instructs the financial engine to pull only those transactions with the specified Client Id. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Client id.
- CompanyName = This instructs the financial engine to pull only those transactions with the specified Company. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Company.
- CashBasis = If you want the values to be run as Cash Basis accounting, then enter 1 as the value.
GetNetIncome()
The purpose of this function is to obtain from the Workamajig financial engine the Net Income as of a specified date. No account range is specified since the system already knows to collect information for all Revenue and Expense accounts.
The format of GetNetIncome () is as follows:
GetNetIncome (As Of Date,ClassId, Department,Office, ClientId,ProjectNumber, CompanyName,CashBasis) where:
- As Of Date = This instructs the financial engine to total all Net Income through this date. This is a required field in this function.
- ClassId = This instructs the financial engine to pull only those transactions with the specified ClassId. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of class.
- Department = This instructs the financial engine to pull only those transactions with the specified Department. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of the Department.
- Office = This instructs the financial engine to pull only those transactions with the specified Office. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Office.
- ClientId = This instructs the financial engine to pull only those transactions with the specified Client Id. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of client ID.
- ProjectNumber = This instructs the financial engine to pull only those transactions with the specified Project Number. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Project Number.
- CompanyName = This instructs the financial engine to pull only those transactions with the specified GL Company Name. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of GL Company Name.
- CashBasis = If you want the values to be run as Cash Basis accounting, then enter 1 as the value.
Get Retained Earnings
The purpose of this function is to obtain the Retained Earnings from the Workamajig financial engine as of a specified date. No account range is specified since the system already knows to collect information for all Revenue and Expense accounts.
The format of Get Retained Earnings is as follows:
Get Retained Earnings (As Of Date, ClassId, Department, Office, ClientId, Project Number, CompanyName, CashBasis) where:
- As Of Date = This instructs the financial engine to total all Retained Earnings through this date. This is a required field in this function.
- ClassId = This instructs the financial engine to pull only those transactions with the specified ClassId. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of class.
- Department = This instructs the financial engine to pull only those transactions with the specified Department. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of the Department.
- Office = This instructs the financial engine to pull only those transactions with the specified Office. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Office.
- ClientId = This instructs the financial engine to pull only those transactions with the specified Client Id. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of client ID.
- ProjectNumber = This instructs the financial engine to pull only those transactions with the specified Project Number. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of Project Number.
- CompanyName = This instructs the financial engine to pull only those transactions with the specified GL Company Name. This is an optional field in this function. Leaving it blank will tell the financial engine to return all entries regardless of GL Company Name.
- CashBasis = If you want the values to be run as Cash Basis accounting, then enter 1 as the value.
To calculate a worksheet or workbook now
Below are the keyboard shortcuts with the action that will be performed:
Press F9: Calculates formulas that have changed since the last calculation and formulas dependent on them in all open workbooks. If a workbook is set for automatic calculation, you do not need to press F9 for calculation.
Press SHIFT+F9: Calculates formulas that have changed since the last calculation, and formulas dependent on them in the active worksheet.
Press CTRL+ALT+F9: Calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.
Press CTRL+SHIFT+ALT+F9: Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.
Depending on what version of Excel you have, there is also a button in the Formulas section to Calculate Now or Calculate Sheet.
[Insert Image]
Additionally, here is a guide from Microsoft on ways to recalculate a spreadsheet:
https://msdn.microsoft.com/en-us/library/office/bb687891.aspx
Troubleshooting
Please keep in mind that this section is designed for Excel Gurus. However, if you do have issues with a specific calculation, field or function, please email us a copy of your Excel file to support@workamajig.com.