Integrating with Excel

Integrating with Excel

We designed Excel Integrations to allow users to import their data from essentially any accounting software that can export data to a spreadsheet. To successfully import your data, you will need to first understand how Castaway Excel templates are structured, so you can line your data to ensure that it meets the template’s requirements and makes it readable to Castaway. 

Selecting Excel as your Integration method

1. Click on New Project and select the Excel icon
2. Complete the project details and select Build My Project
3. You are now in the Integrations workspace. You can also find your way here by going to Forecast > External Sources
4. Click on the Excel icon and select Build Excel Template (this will download an Excel template)

Navigating the Excel Template

The Castaway-generated template comes with 2 sheets – Data load and Instructions. The Data load sheet is for you to paste and massage your Actuals data while the Instructions sheet provides you with a guide on how to get started. Read the Instructions sheet carefully to understand what you need to do to get your template ready to import back into Castaway.

Row 1 of the Data load sheet should already be filled with a Header row, in the following order:
Type, Account, Name, followed by your Opening and Actuals months (if you have already rolled your project forward).

IMPORTANT

If you intend to reimport actuals on a regular basis using the same Chart of Accounts, include account codes in the Account column, and make sure that each code is unique and not duplicated. If you do not include an account code, Castaway will not be able to remember your mapping.

Excel Template Rules

In order to import data smoothly, Castaway has some rules for our Integrations templates.

You cannot:

1. Remove or edit Row 1. The Row 1 headers are how Castaway can select the correct information to read and import. Do not edit the headers or add another row above it.
2. Move the Data load sheet. The Data load sheet must always be the first sheet in your template file.
3. Add columns in between columns A, B and C. These columns will need to always remain as Type, Account and Name to be read.
4. Have a misspelt Type or a Type that is not listed as an option. Remember, the options are Income, Expense, Asset, Liability, Equity, Driver or Ignore. If your file import is producing errors, check the spelling of your types.
5. Rename the Excel file when re-importing. Ensure that the file name remains the same throughout your process, as a newly named template can be read as a new set of data.
6. Go over 100 characters for Account names. Castaway caps Account names at 100 characters

You can:

1. Add more sheets to your template. The additional sheets can be your monthly data, or working paper notes, or even a diary entry! The choice is yours.
2. Include Excel formulas! If you have multiple months of data you’d like to arrange in the template, but do not want to copy & paste them, you can insert formulas like SUMIF or VLOOKUP to help you speed up the process.
3. Delete the Instructions sheet if it no longer serves you.
4. Add notes to your Data load sheet. If the notes you enter do not fall under the monthly headers or under one of the other header titles, the template will pass Castaway’s validation check.

Additional:

Entering Ignore as a type is the same as leaving it empty or blank. We offer this as an option to users who want more clarity and prefer to have a labelled type, rather than leaving the cell empty.

Importing the Excel template

Once you have prepared your template, return to Castaway to begin importing your data.

1. In your Project, go to Forecast > External Sources and select the Excel icon
2. Select your Excel file to import