Excel

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.


Screen Shot 2021 01 29 at 10.49.06 am

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.

excel import example template image2

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.

  1. Download the Spotlight import template.
  2. 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.
  3. In column C onwards, enter the required dates in the format mmm-yy.
  4. 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.
  5. Save the spreadsheet on your computer, then from the Import tab in Spotlight, select the Excel data source and click Choose file.
  6. Browse to and select your saved spreadsheet, then click Open.
  7. 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.

  1. From the Import tab, click the Excel option and download the import template.
  2. 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.
  3. In column C onwards, enter the required dates in the format mmm-yy.
  4. Enter your actual values into the corresponding months from column C onwards.

    Note: You should enter the month-end value.
  5. In the KPI Targets tab, enter the same account names and type you used in step 2.
  6. Enter the required dates in the format mmm-yy into column C onwards
  7. 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.
  8. Save the Excel workbook on your computer.
  9. Open Spotlight, then from the Import tab select the Excel data source then click Choose file.
  10. Browse to and select the saved file, then click Open.
  11. 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.

  1. From the Import tab, click the Excel option and download the import template.
  2. 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.
  3. Enter the required dates in the format mmm-yy into column C onwards.
  4. Enter your actual values into the corresponding months from column C onwards.

    Note: You should enter the month-end value.
  5. In the Non Financial Targets tab, enter the same account names and type you used in step 2.
  6. Enter the required dates in the format mmm-yy into column C onwards
  7. 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.
  8. Open the Non Financial tab, and enter the actual results as of each month.
  9. Save the Excel workbook on your computer.
  10. Open Spotlight, then from the Import tab click Choose file.
  11. Browse to and select the saved file, then click Open.
  12. 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.


Still need help?
Get in touch!