Manual Upload of Client Data (Sage Clients)

We realise that this is not straightforward and can be scary, so we've built a step-by-step guide to walk you through it!

If, for some reason, we are not able to use a direct Sage sync for a client on Silverfin, the following steps should be followed to upload the data to Silverfin manually:

  1. If first year setup in Silverfin, firstly we need to enter the prior year TB to Silverfin. To do this, we should run a Sage TB to ensure the Sage nominal codes are used in the Silverfin file. 

To run a prior year TB if the year end has been run (which is most likely) we will need to use a transactional TB for all transactions to that year end date, prior to the Year End Ledger transfers.

NOTE: If you are running this for a management accounts client, then you need to use year to date data.

To check the filters required to run this trial balance follow the below steps:- 

  • In Sage, click ‘Transactions’ in the left hand menu,
  • Click ‘Filter’ then in the pop up box, enter the following filter boxes – ‘Where’ ‘Date’ ‘Is Less Than or Equal To’ ‘DD/MM/YYYY’ (prior year end date), click ‘Apply’ 

- Ensure the data is sorted so that the latest transaction numbers within the filter are at the top of the list – to do this, click on the ‘No’ data header until the arrow is pointing down the way

NOTE: In most cases, the transactions that appear at the top of the list will be the Sage Ledger Year End from when the year end has been run. If there are others transactions, these are transactions that have been back posted after the Sage year end was run, and these will need to be accounted for in the current financial year manually (via a journal in Silverfin).

  • Scroll down the list, until you see the start of the ‘Ledger Year End Transactions’ and note that transaction number – in my example, number 41203.
  • 2.  Run the trial balance with the info obtained from the above filter.
  • Click ‘Nominal Codes’ in the left hand menu,
  • Click ‘Reports’ from the menu along the top, 
  • Click, ‘Trial Balance’ in the reports list screen, 
  • Choose ‘Transactional Trial Balance’ from the list of available reports, 
  • Populate the criteria for the report as follows: 
  • Transaction Date – 01/01/1980 – PRIOR YEAR END DATE
  • Transaction No – 1 – Transaction number obtained in step 1 (Minus 1) – so in my example 41202
  • Department – leave as default.
  • Click ‘Ok’ 

  • 3.  Click ‘Report to Excel’ (Data to Excel does not work for exporting Trial balances from Sage).
  • 4.  In Excel, we need to remove all formatting, in Excel press ‘CTRL + A’, then unselect ‘Merge & Center’ from the Alignment box. Delete the unnecessary rows/columns so all that is left is the headers and the TB data(you should delete any lines which do not have a figure in the debit or credit value).

Save the TB. This TB is then ready to import to the prior year in Silverfin. 

  • 5.  Import the TB to Silverfin:-
  • Open the client in Silverfin, and select ‘Upload file’ 

- Find the TB you created from your files-

- Select the column names from the drop down, then ‘Next Step’

6. When the data has imported:-

  • The next screen should have a ‘This data is OK’ message, and click ‘Next Step’
  • In the next screen, you will need to select the prior year end date.
  • Prepare the mapping as usual, ensuring P&L to P&L, and Balance Sheet to Balance Sheet.

7.  Agree TB to PY final accounts. 

  • The FULL year end adjustments will need to be posted to Silverfin as a journal (unless, the balance sheet imported already has the balance sheet adjustments posted – then you will just need to post the P&L adjustments).
  • Full adjustments are required, as when posting year end adjustments to Sage, the P&L is not adjusted for. 

8.  Ensure the Balance Sheet (including equity/capital), Profit and Loss figures agree to the prior year signed accounts.  

9.  Next step – import the current year nominal ledger into Silverfin:

  • Return to Sage, and click ‘Nominal Codes’ from the left hand menu,
  • Click ‘Reports’ from the menu along the top, 
  • Click, ‘Nominal activity’ in the reports list screen, 
  • Choose ‘Nominal Activity – Excluding No Transactions’  from the list of available reports, 
  • Populate the criteria for the report as follows:
  • Nominal Code – BLANK - 9999999
  • Transaction Date – CURRENT FINANCIAL YEAR
  • Transaction No – 1 – 9999999
  • Click ‘Ok’ 

10.  Click ‘Data to Excel’

11.  In Excel, we need to delete the following columns: 

  • Company.Name
  • NominalRecord.Balance
  • TransactionNominal.BroughtForwardFlag
  • TransactionNominal.DepartmentNumber (unless client uses department codes)
  •  TransactionNominal.VATReconciledStatus
  •  TransactionNominal.BankReconciledStatus
  • Company.EndOfReportBanner

We also need to delete the first row of data so the first row we are then left with is the column headings. 

12.  Due to the way that data is exported from Sage, it will include deleted transactions (and a contra to remove them). To remove them, we should sort ‘TransactionNominal.Reference’ A-Z, then to delete the ‘Cancel’ Rows. 

13.  Check your data still balances after deleting the cancelled transactions by highlighting ‘TransactionNominal.Account’ and checking the Sum is 0. 

14.  Now, you need to manually enter the prior year reserves movement (including dividends paid, any other reserves movemnts)/P&L value as a manual line at the bottom of the data (profit as a negative value and loss as a positive value).

You need to populate the following columns:

  • NominalRecord.AccountReference – this should be the retained earnings N/C from Sage
  • NominalRecord.AccountName – this should be the exact name as from Sage of the above nominal code. 
  • TransactionNominal.Amount – the prior year retained earnings/profit and loss figure
  • TransactionNominal.AccountDate – First date of the financial year.

Save the document. It is now ready to import to the current year in Silverfin. 

15.  Open the client in Silverfin, and select ‘Add new period’. 

And select, ‘Upload file’ 

Find the TB you created from your files

- Select the column names from the drop down/create custom via ‘Other Value’ then ‘Next Step’

When the data has imported:- The next screen should have a ‘Attention! The sum of all lines is different from 0’ message. The figure here should be the same as the reserves movement/P&L from the prior year. Any new nominal codes will also be highlighted to you. Click ‘Next step’- In the next screen, you will need to select the current year end date.

- Prepare the mapping as usual for any new nominal codes, again, ensuring P&L to P&L, and Balance Sheet to Balance Sheet.

In the next screen, if you have completed any reconciliations in the prior year that you wish to copy forward at this point, you can do so now, or otherwise click ‘Don’t copy’. Any reconciliations / templates completed in the prior year can be copied forward at a later point also. 

16.  Next step, is to roll forward your opening balance sheet balances from the prior year:

  • In the current year, click on ‘Edit Client’, select the 'Periods' tab / section then under ‘Fiscal years’ then click on the ‘pencil’ to edit the current year, click on the arrow so that it turns black. Then click on the tick to save. Then ‘Save changes’ at the top of the screen. 

17.  Check your data agrees to Sage. The quickest way to do this is to check your key financials agree: 

Run a balance sheet, and profit and loss for the financial year from Sage and ensure they agree.

If at point 1 you noticed any back postings, you will need to post these (in the current financial year) to agree Silverfin to Sage. 

18.  In the current year, check the retained earnings figures agree to the prior year signed accounts (you will need to journal any PY dividends from dividends to reserves).

19.  Good to go with your accounts preparation. You will have the full functionality of the ‘view ledger’ within Nominal Codes as we have imported the full nominal ledger from Sage.