If Spotlight does not have a direct integration to the software you’d like to report on, you can use the Excel Import function to import any financial or non-financial data. All you need to do is download and populate the Excel template (from the Import screen) with your own data.
Understanding the spreadsheet
There are seven tabs in the workbook - three of these tabs are used to import financial data and the remaining four are for non-financial figures. You don't need to use all tabs to import via Excel, instead you can simply include the ones for the data you want to import.
Financial tabs
Note: For profit and loss accounts, you should use the monthly values. For balance sheet accounts, you should use the cumulative values.
Actuals - Actual figures from the current financial year profit and loss and balance sheet reports.
Budget - Monthly profit and loss Budget figures for the current financial year.
Actuals LY - Actual figures from last financial year's profit and loss and balance sheet reports.
Cash - Cash movements in the current financial year for each profit and loss and balance sheet account.
Cash LY - Cash movements in the previous financial year for each profit and loss and balance sheet account.
Non-financial tabs (Reporting, Dashboards and Multi only)
KPI Actuals - Month end results for your Key Performance Indicators (KPIs).
KPI Targets - Monthly targets for your KPIs.
Non Financial - Month end results for non-financial data.
Non Financial Targets - Monthly targets for non-financial data.
The information in each tab
Each tab should contain at least three fixed columns:
Note: If you use the sample import template, you should delete the sample text shown in row 2.
Column A - Account Name - The account name as shown in your chart of accounts.
Column B - Account Types - The type of account. The following types are available:
Trading Revenue
Fixed Asset
Cost of Sales
Current Liability
Overheads
Non-Current Liability
Current Assets
Equity
Bank
KPI
Non-Current Assets
Non-Financial
Column C onwards - Monthly values - Enter the month and year in the column heading, and the values for each account below.
Note: For profit and loss accounts, enter the monthly value. For balance sheet accounts, enter the year to date value.
Account types will be case insensitive unless otherwise specified.
Column Name: Type or Account type
Revenue
revenue trading revenue sale other revenue other income
Overhead
overhead
Expense
expense operating expense fixed expense opex bank revaluation unrealised currency gains realised currency gain
Current Liability
current liability current liablities liability liabilities accounts payable payable creditor unpaid expense claim wage payable wages payable gst
Current Asset
current asset asset accounts receivable receivable debtor
Bank
bank bank account credit card
Fixed Asset
fixed asset
Liability
rounding tracking
Non Current Liability
non-current liability noncurrent liability non current liability non-current liabilities noncurrent liabilities non current liabilities term liability loan
Non Current Asset
non-current asset noncurrent asset non current asset term asset intangible asset
Equity
retained earning equity
Direct Cost
cost of sale direct cost
Data to gather before import
You can gather these balances by downloading a trial balance or the monthly profit and loss, balance sheet and cash summary reports from any accounting system.
Monthly accrual balances - Profit and loss and balance sheet accounts.
Monthly cash balances - Profit and loss and balance sheet (optional).
Monthly profit and loss budget - Budget values for profit and loss accounts only (optional).
Note: Although Spotlight is designed to report monthly, you can import quarterly or annual balances. Just enter the figures available to the last month within the relevant quarter or year.
Importing financial data
You can also view this information in a quick video demo.
Open the appropriate worksheet tab that you would like to import data for. Note: If you wish to import KPI or Non-financial data, we have provided specific instructions further down in this article.
In column C onwards, enter the required dates in the format mmm-yy.
Complete the remaining data. All tabs use the following structure: Note: You should only import account balances. Do not include any total values in the import. Do not include the Current Year Earnings row, Spotlight calculates this automatically. Column A - Account name - The account name you would like to appear within your Spotlight reports. Column B - Account type - The type of account you're importing. For information about the account types available, please refer to the account type list. Column C - Monthly values - The appropriate values for the tab type - actual values, cash movements, budget or target values.
Save the spreadsheet on your computer, then from the Import tab in Spotlight, select the Excel data source and click Choose file.
Browse to and select your saved spreadsheet, then click Open.
Once the file uploads, to complete the process click Import.
Note: Once the import is complete, if any errors are reported please check the error message and correct any issues before re-importing the Excel file.
You will need to import updated values each time you run the report. If any accounts have not been mapped correctly, you can change the Report Code in the Customise Data tab to correct the mapping.
Import KPI accounts via Excel
Note: This applies to Spotlight Reporting, Spotlight Dashboards and Spotlight Multi only.
From the Import tab, click the Excel option and download the import template.
Open the template and in the KPI Actuals tab, enter the name and account type of each account.
Column A - Account Name - The account name you want to appear on your Spotlight reports. Column B - Account Type - This should always be KPI.
In column C onwards, enter the required dates in the format mmm-yy.
Enter your actual values into the corresponding months from column C onwards. Note: You should enter the month-end value.
In the KPI Targets tab, enter the same account names and type you used in step 2.
Enter the required dates in the format mmm-yy into column C onwards
Enter your target values into the corresponding months from column C onwards. Note: If required, you can enter future targets. These will appear in Spotlight in yellow, as with financial budgets.
Save the Excel workbook on your computer.
Open Spotlight, then from the Import tab select the Excel data source then click Choose file.
Browse to and select the saved file, then click Open.
Once the file uploads, to complete the process click Import.
Note: Once the import is complete, if any errors are reported please check the error message and correct any issues before re-importing the Excel file.
You will need to import updated values each time you run the report. If any accounts have not been mapped correctly, you can change the Report Code in the Customise Data tab to correct the mapping.
Import Non-financial accounts via Excel
Note: This applies to Spotlight Reporting, Spotlight Dashboards and Spotlight Multi only.
From the Import tab, click the Excel option and download the import template.
Open the template and in the Non Financial tab, enter the name and account type of each account.
Column A - Account Name - This is the account name you want to appear on your Spotlight reports. Column B - Account Type - This should always be Non-Financial.
Enter the required dates in the format mmm-yy into column C onwards.
Enter your actual values into the corresponding months from column C onwards. Note: You should enter the month-end value.
In the Non Financial Targets tab, enter the same account names and type you used in step 2.
Enter the required dates in the format mmm-yy into column C onwards
Enter your target values into the corresponding months from column C onwards. Note: If required, you can enter future targets. These will appear in Spotlight in yellow, as with financial budgets.
Open the Non Financial tab, and enter the actual results as of each month.
Save the Excel workbook on your computer.
Open Spotlight, then from the Import tab click Choose file.
Browse to and select the saved file, then click Open.
Once the file uploads, to complete the process click Import.
Note: Once the import is complete, if any errors are reported please check the error message and correct any issues before re-importing the Excel file.
You will need to import updated values each time you run the report. If any accounts have not been mapped correctly, you can change the Report Code in the Customise Data tab to correct the mapping.
Troubleshooting - Data not importing
For information about the areas to check if your Excel data doesn't import as expected, please refer to our Troubleshooting Excel Imports article.
