Import historical data

Modified on Thu, 12 Dec at 9:33 AM

Disclaimer

All screenshots in the article were taken in the Dutch version of Yuki.

In Yuki you can import transactions which occurred before to the start date of your administration in Yuki. You import the historical data of one or more years from the old software program so you immediately have comparative figures In Yuki. You have to follow a number of steps in order to import historical data. Yuki provides you with an Import Wizard that will guide you step by step through the entire import process.

Only a user with a 'Back office' role in the domain or the 'External accountant' role can import historical data.

ATTENTION!

The opening balance that must be entered in Yuki consists of the closing balance of the financial year that is before the set first financial year of your administration.

If the start date of your administration in Yuki is 01-01-2022, the closing balance of the 2021 financial year must be entered.

If you use a so-called XAF Audit file, historical data from almost all accounting programmes (including Yuki) can be imported.

If you use a CSV file, currently only historical data from Yuki, AccountView, Unit4, AFAS, Exact Online and Twinfield can be imported.

When following the steps of the Import Wizard you wil be asked whether you want to import the historical (GL account) transactions, outstanding debtors, creditors and/or fixed assets. Below you will find the relevant example files:

Historische grootboektransacties (XAF- of CSV-bestand) (Historical GL account transactions (XAF file or CSV file))
Openstaande debiteuren (CSV-bestand) (Outstanding debtors (CSV file))
Openstaande crediteuren (CSV-bestand) (Outstanding creditors (CSV file))
Vaste activa (CSV-bestand) (Fixed assets (CSV file).

The Import Wizard consists of nine steps that you have to follow. Use the menu below in order to navigate quickly to the explanation of a step in this article:

The CSV files must be structured according to the Yuki import format.

After the import of the historical data it is possible to:

View history
Adjust imported files
Delete entire import
Only delete transaction import file.

The Iimport Wizard can be started from two places in Yuki:

Click on the Back office icon in the navigation bar and then, in the now-opened screen, click on Import
Hover your mouse over the Financials icon in the navigation bar and then click on Import.

In the now-opened screen, click on the Import historical data button.

The following screen is opened:

First select an administration when the accounting records of more than one administration are kept in the domain.

Then you have to continue with the first of in total nine steps of the Import Wizard. Below each of the nine steps of the Import Wizard are described in more detail.

Step 1: Prepare import

Before you start with the import you have to make sure that the administration settings, your chart of accounts and certain others things are configured correctly. You can pause the import process but you will go must faster through this process when you have prepared a number of things.

Make sure your company profile is complete.
The most important thing is that you have chosen the correct legal form and have set the start date of the first financial year. You also have to check whether the shareholders, owner/director(s) and possible participations have been filled in.
Create all bank accounts (and credit cards) that you have used in the old administration and still have an outstanding balance.
When creating a bank account a GL account is also created at the same time. You need this account later during the import of the historical data.
Go through the chart of accounts and make sure that you have created and activated (enabled) all GL accounts that you have used before in the old administration.
When you're dealing with fixed assets in your administration then make sure the 'group settings' of all fixed assets GL accounts are defined correctly.
It is important for the import that it is clear which depreciation accounts belong to which investment accounts.

When you have completed all the preparations you can proceed with the next step.

Step 2: Import historical (GL account) transactions (XAF file or CSV file)

First we are going to import the historical transactions from the 'old' software program. This can be done in two ways:

XAF import
If you want to import historical transactions, you need to create a separate XAF file (from 3.0 onward) in the "Old" program for each year you want to import and import them t at step 2 all at once.
CSV import
The second option is to download a CSV export from the 'old' program, convert this to a fixed Yuki CSV format and then import this again in step 2 of the historical import.
When you want to import more than one historical year you have to include all the years that you want to import in one file, it is not possible to include a specific year later. Make sure the CSV file is as complete as possible.

ATTENTION!

It is not allowed in Yuki to import the historical transactions from the old program that have been posted to the following general ledger accounts:

09999 Result current financial year
23010 Account unknown
23020 Unclear transactions.

Below the columns of the CSV file are described in more detail.

The Yuki import format for historical (GL account) transactions

The import file must be a CSV file that contains the following 8 columns:

Ledger account code	Mandatory	Ledger account account from old software program
Ledger account description	Mandatory
Transaction date	mandatory	dd-mm-jjjj or d-m-jj, separation by - or /
Reference	Optional	Invoice number or accounting document number
Amount	Mandatory	#####,## or ####.##, no thousand separator allowed
Transaction description	Mandatory
Contact name	Optional
Contact code	Optional

The first line of the file must contain the column headings (column names). You're free to determine the column names but the order and the number of columns must always be the same as described above.

Furthermore the balance of all transactions must be 0,00 on each transaction date. If that is not the case or when there are some small rounding differences then you can still correct this in one of the next steps. You can create such a CSV file simply in Excel and then save it as .CSV.

If you don't use Excel to create a CSV file then make sure to create a semicolon separated CSV file.

Drag the transaction import file(s) to the grey area or click the grey area in order to search for and upload the file(s) on your computer. A check is now performed to verify whether the file(s) meet all the technical requirements. After that all historical (GL account) transactions are imported.

You will receive an email from Yuki when the import of the historical transactions fails. The email contains the reason why the import was not successful.

Step 3. Confirm that the import was successful

In this step all transactions that were included in the transaction import file are shown. These should be identical one-on-one. The columns that are shown can be sorted ascending or descending in order to facilitate the check of the import.

In case the transactions are not in balance (i.e. the balance is not 0,00) on a certain date you can click on the button at the top of your screen in order to 'repair' the imbalance. Yuki will record an additional entry on '23010 Unknown account' for each date on which an imbalance occurred.

Do you notice too many errors or is the import file incomplete then you can delete the transaction import file again that you have imported in this step. All entries that were possibly already recorded in step 5 of the Import Wizard will also be deleted.

You can proceed with the next step when the import is correct.

Step 4. Match with chart of accounts in Yuki

In this step you have to match the chart of account from the 'old' software program with the chart of accounts in Yuki. You can match the GL accounts automatically or manually. Also you can export the matched GL accounts and use them again in an other domain by importing them.

When matching the GL accounts the following GL accounts in Yuki cannot be matched (these are therefore not shown):

23000 Internal transfers of funds
23300 Suspense account debit card payments
23310 Suspense account credit card payments
23320 Suspense account iDEAL payments
23330 Suspense account online payments
09999 Results current financial year.

The reason for this is that the suspense accounts above create an open item that has to be matched. However, Yuki will not create open items prior to the start date of your administration. Besides these suspense accounts are also included in the checks of the quality monitor which would affect the total score (score of all checks). The 'Results current financial year' account is used by Yuki to calculate the result (of the profit-and-loss account) and subsequently record it on the relevant balance sheet account.

Match automatically

When you used a chart of accounts in your old software program that is very similar to the one in Yuki then you can immediately match the GL accounts with the same code automatically by clicking on the Match GL accounts by code button at the top of your screen.

Match manually

Match the old GL accounts one by one to the corresponding GL account in Yuki. The GL account in the old chart of accounts at the top of your screen (left column) is already selected automatically. Navigate to the GL account in the chart of accounts of Yuki (right column), If necessary use the search box in the upper right of your screen. Then click on the Yuki GL account.

A notification will immediately be displayed on your screen indicating that the matching (of the two GL accounts) will be saved.

The old GL account to be matched will disappear.

When the GL account that you want to match is not yet available in the chart of accounts of Yuki then click on the icon to the right of the old GL account description.

This GL account is now hidden temporarily. When you are finished with matching all the GL accounts then you can make the account(s) for which you still have to create a GL account in Yuki visible again by clicking on the Show hidden GL accounts button.

A notification indicating that the matching is completed will be displayed on your screen when all the GL accounts (also the hidden ones) are matched.

Export and import matchings

When you want to use the Import Wizard again in another domain you can export the created matchings by clicking on the Export button at the top right of your screen and save them as a CSV file on your computer. Later you can then reuse the file in a new domain by importing it there by clicking on the Import mappings button.

Check and remove matchings

Double check the created matchings before you proceed with the next step. You can do that by selecting the Already matched option in the drop-down menu in the upper left of your screen.

Here you see the old GL account together with the matched GL account in Yuki. Click on the red cross in order to remove the matching. Subsequently you have to select the Pending to match option in the drop-down menu in order to create the matching again.

You can proceed with the next step when the matchings are complete and correct.

Step 5: Create entries

Now that the import is complete and all old GL accounts are matched with the GL accounts in Yuki you can create entries in Yuki that are based on the imported transactions.

You have two options:

Transaction per line For each transaction that is included in the import file a separate transaction line will be created in Yuki.
Lines with an amount of 0,00 will be skipped. All transaction lines with the same date will be recorded as one general journal entry of the 'Opening balance' type.

If contact details were also included in the imported transactions then Yuki will also create those contacts.
Balance per month. All transactions of each GL account will be summarised as one transaction line per month. You can still see which historical transactions are included in this 'aggegrated' transaction per month.

It is advisable to select the second option when the transactions import file is very large (> 2000 transactions). The first option is preferable when the administrations are smaller.

Good to know:

A notification indicating that the entries are recorded successfully will be displayed on your screen.
All VAT transaction lines (purchase and sales) will be recorded on 18990 VAT returns transfer account'.
The following GL accounts are not recorded on the matched GL account in Yuki but on a temporary GL account. This applies to:
- Fixed assets GL accounts (recorded on GL account '02999 Suspense account opening balance assets')
- Depreciation GL accounts (balance sheet) of fixed assets (recorded on GL account '02999 Suspense account opening balance assets')
- Debtors (recorded on GL account '13001 Suspense account opening balance debtors')
- Creditors (recorded on GL account '16001 Suspense account opening balance creditors')
- Bank accounts (recorded on GL account '11099 Suspense account opening balance banks').
  
  This is because in the next steps (import of outstanding debtors, outstanding creditors and fixed assets) the individual items that were still open on the closing balance are recorded on those correct GL accounts and then contra entries are recorded on the suspense accounts above.

When errors occur during the import of the financial data all unprocessed transactions can be imported again in step 5 (Create entries in Yuki). However, this only involves transactions of which the error is solved by changing the matching (mapping) with the GL accounts.

The following screen is opened when you click on the Create entries button (the 'Transaction per line') option):

TIP!

You will receive an email from Yuki when the import of the transaction import file begins and ends.

Refresh your screen so that the following screen is opened::

Here you can see all created journal entries by transaction line.

Now proceed with the next step.

Step 6: Import outstanding debtors (optional)

When there are no outstanding debtors you can immediately proceed with the next step by clicking on the Next step button.

In this step the invoices of customers that were still outstanding on the closing balance will be created. Use a CSV file in the following format:

The Yuki import format for outstanding invoices

The import file must be a CSV file that contains the following 10 columns:

Ledger account code	Mandatory	Must be the account from the old software program that was matched to Yuki's account 13000
Ledger account description	Mandatory
Invoice date	Mandatory	dd-mm-jjjj or d-m-jj, separation by - or /
Outstanding amount	Mandatory	#####,## of ####.##, no thousand separator allowed
Invoice description	Mandatory
Reference	Mandatory	Invoice number or accounting document number
Due date	Optional	dd-mm-jjjj or d-m-jj, separation by - or /
Contact name	Mandatory
Contact code	Optional
Bank account number	Optional

Drag the import file(s) to the grey area or click the grey area in order to search for and upload the file(s) on your computer. A check is now performed to verify whether the file(s) meet all the technical requirements. After that all outstanding debtors are imported.

During the import of the file a transaction line will be created directly on the invoice date for each outstanding debtor. The 'Suspense account opening balance debtors' is used as contra account. If everything went well the balance of that suspense account is 0,00 again. If this is not the case then the accounts receivable balance in the general ledger transactions doesn't reconciliate with the balance of all outstanding accounts receivable.

A notification indicating that the outstanding invoices were created successfully is displayed on your screen when you click on the Next step button.

Step 7: Import outstanding creditors (optional)

When there are no outstanding creditors you can immediately proceed with the next step. Were there are still outstanding invoices of suppliers then proceed with step 6 but then for creditors.

A notification indicating that the outstanding invoices were created successfully is displayed on your screen when you click on the Next step button.

Step 8: Import fixed assets (optional)

In this step possible fixed assets and the corresponding depreciated amounts are imported. Use a CSV file in the following format:

The Yuki import format for fixed assets

The import file must be a CSV file that contains the following 11 columns:

Ledger account code	Mandatory	Must be an account from the old software program that was matched to an asset account from Yuki in step 4
Ledger account description	Optional
Asset description	Mandatory
Asset reference or number	Optional
Purchase date	Mandatory	dd-mm-jjjj or d-m-jj, separation by - or /
Purchase amount	Mandatory	#####,## of ####.##, no thousand separator allowed
Start depreciation	Optional	dd-mm-jjjj or d-m-jj, separation by - or /
Depreciation method	Optional	Must be blank or "L" or "D" for Linear or Degressive
Depreciation in years	Mandatory	Must be an integer, such as 3, 5, 10, 33 etc. For depreciation at 3%, use 33 years.
Residual amount	Mandatory	Must be 0.00 or higher, but not higher than purchase amount. If you do not wish to depreciate, make this amount equal to the purchase amount
Already depreciated	Mandatory	Must be 0.00 or higher, but not higher than purchase amount - residual amount.

Drag the import file(s) to the grey area or click the grey area in order to search for and upload the file(s) on your computer. A check will now be performed to verify whether the file(s) meet all the technical requirements. After that all fixed assets are imported.

During the import a general journal entry will be created per asset.

Also a general journal entry for the cumulative depreciated amount will be created for the assets on which an amount was already depreciated.

Each time the 'Suspense account opening balance assets' is used as contra account. After this import the balance of this suspense account should be 0,00. If this is not the case then the balance of the general ledger transactions on the asset accounts doesn't reconciliate with the balance of the assets import.

A notification indicating that the assets were created successfully is displayed on your screen when you click on the Next step button.

Step 9: Complete the import

In the final step of the import process Yuki will check the balance on the start date of the following GL accounts:

11099 Suspense account opening balance banks
13001 Suspense account opening balance debtors
16001 Suspense account opening balance creditors
02999 Suspense account opening balance assets.

When a cross is shown behind one or more GL accounts it means that the balance on the start date of the administration is not in balance ór the relevant import file has not been imported yet. By clicking on the relevant suspense account you can check the balance.

After a historical import the 'Suspense account opening balance banks' can show that it is not in balance. Often this is because the opening balance of the relevant bank account(s) is not filled in.

View history

After you have gone successfully through all the steps of the Import Wizard you can view all the entries/general journal entries created by Yuki by clicking on the History button.

The following screen is opened:

Adjust imported files (XAF and/or CSV)

It may be the case that you want to adjust one or several import files and then upload them again. You have to open the Import Wizard again by clicking on the BO button at the top of your screen. Subsequently, click on Import on the left of your screen and then click on the Import historical data button at the top of your screen. The final step of the Import Wizard 'Step 9: Complete the import' is opened again.

In the historical Import Wizard you can go back to previous steps, from step 9 you can click on step 8, 7, 6, etc.

In the upper left of steps 3, 6, 7 and 8 the Delete button is shown.

Yuki will delete the relevant import file and all corresponding entries/general journal entries by clicking on the Delete button.

Delete entire import

When you want to do the entire import of the historical data again then step by step you have to go back through all the steps in the Import Wizard and delete the relevant imported files.

However, the GL account matchings will remain.

When you want to adjust the matchings you have to delete the transaction import file and subsequently upload it again. You have to go through all the steps again and in step 4 you can adjust the matchings.

Only delete transaction import file

When the import of the outstanding debtors, outstanding creditors and fixed assets is correct but you want to upload the balance sheet transactions again then you can click on step 3 at once from the historical Import Wizard.

In the screen now opened, click on the Delete button. The following screen is opened:

ATTENTION!

If you have imported the outstanding debtors, outstanding creditors or fixed assets, the screen below appears when you have confirmed that you want to permanently delete this data:

By clicking on the No, only transactions button you will only delete the transaction import file. The other import files and entries/general journal entries will remain.

By clicking on the Yes button you will delete all the import files and the entries/general journal entries at once.

The following screen is opened:

TIP!

You will receive an email from Yuki when the deletion of the transaction import file or all import files begins and ends.