Import stock opening balances

Find this screen

Open: Stock Control > Utilities > Import and Export > Import Opening Balances.

Open: Stock Control > Utilities > Import and Export > Import Batch/Serial No Opening Balances.

How to

Create an import stock file for stock opening balances

You can import opening balances for non traceable stock items, or for batch/serial traceable stock items.

Note: You can't change the date of the imported opening balance transaction. The date from your PC at the time of import is used as the date of the opening balance transaction.

You can import stock opening balances using a CSV Comma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. file.

Download the import information and example files:
- For non traceable stock items:
  - Stock Opening Balance import format (XLS) for non traceable items: Details of the information you need to include in the file.
  - Stock Opening Balance example CSV file for non traceable items: Use this to import from CSV format.
- For batch/serial traceable stock items:
  - Stock Opening Balance import format (XLS) for batch/serial traceable items: Details of the information you need to include in the file.
  - Stock Opening Balance example CSV file for batch/serial traceable items: Use this to import from CSV format.
Create your import file in the format of the CSV example file.
Read the information in the import format file (XLS), and make sure your information is correct and all mandatory fields are included.
- For non traceable stock items, each opening balance must have a Item, Warehouse, Bin, Quantity and Cost.
- For batch/serial traceable stock items, each opening balance must have a Item, Warehouse, Bin, Quantity, Cost, Type and Tnumber.
Save your import file.

Validate your import file

You can validate the import file for non traceable stock items, or for batch/serial numbered traceable stock items:

Open: Stock Control > Utilities > Import and Export > Import Opening Balances.

Open: Stock Control > Utilities > Import and Export > Import Batch/Serial No Opening Balances.

You can choose to validate the import file, which checks that the records are in the correct format before you import. Doing this doesn't import any information.

Select Validate records only and click OK.
Browse to the import file you created, select it and then click Open.

Sage 200 checks the file. If there are invalid records, the reason for the failure is shown in the error log.
Click Print Error Log to see why the record(s) are not valid.
Make any corrections required to the records in your import file.
To re-validate your import file, repeat this process.

Import stock opening balances

You can import the stock item opening balances for non traceable stock items, or for batch/serial numbered traceable stock items:

Open: Stock Control > Utilities > Import and Export > Import Opening Balances.

Open: Stock Control > Utilities > Import and Export > Import Batch/Serial No Opening Balances.

Select Validate and import records.
Choose whether you want to create bins for stock items, if the bins are in the import file but not in Sage 200. To do this, select Create Bins automatically if they have not already been set up.
If you have enabled Integrate stock management with the nominal ledger (in Stock Control Settings), you'll see options for the Opening Stock Suspense Account.
- Select the Suspense Account that will be used for the nominal postings.
  
  Nominal postings will be made to credit the Suspense Account you select here and debit the Asset nominal account you've specified for the stock item in the import file. This suspense nominal account should have a Profit and Loss report category.
- Enter a Narrative if required.
  
  The narrative will be applied to all the nominal postings for the Asset nominal account, and will override any narratives you've entered for stock items in the import file. You can leave this blank to use the narratives from the import file.
Note: After importing the opening balances, we recommend that you produce a trial balance in Sage 200 and compare this to the trial balance you used for your opening balance figures. If any adjustments are required, you can enter these using a nominal journal entry.
Click OK.
Browse to the import file you created, select it and then click Open.

Sage 200 checks the records and imports the valid ones.

Note: If you get a message "Unable to process the specified file", make sure your import file is closed and isn't open in any applications.
If you have invalid records, click Print Error Log to see the reasons. Make required corrections to the records in your import file.

Tip: We recommend that you remove those records that were successfully imported. As they can't be imported a second time, this stops them cluttering up your report.
Repeat until all records are successfully imported.

Note: The import may take a long time to complete if you have a large number of items in the import file. For example, it might take 1 minute per 1,000 items in the import file. Therefore, it's a good idea to remove any unnecessary items from the import file. So if you're only updating 10 items in a file that has 10,000 items, then edit the import file so it only contains those 10 items.

Useful info

Importing opening balances

Opening balances update the quantity in stock and the buying (cost) price (used to value your stock). You enter the balance for each warehouse and bin (if the item's stored in more than one).

You can only import an opening balance once per stock item per warehouse or bin.

The stock item must already exist and be stored in the specified warehouse(s).

Provided the Stock Control module is set to use multiple locations, you can create new bins for stock items. The system uses the bin name specified in the import file to create the location.

Note: You can't change the date of the imported opening balance transaction. The date from your PC at the time of import is used as the date of the opening balance transaction.

If you have enabled Integrate stock management with the nominal ledger (in Stock Control Settings), then nominal postings will be made for the stock opening balances. Nominal postings will be made to credit the Suspense Account you select here and debit the Asset nominal account you've specified for the stock item in the import file. The postings to the Asset nominal account will use the Narrative that you've entered here, or if you leave this blank then the postings will use the narrative from the stock item in the import file.

Where stock items maintain a movement history, an entry is added to the stock item history file.

What happens when

What happens when I validate the file?

Sage 200 checks for the following:

Whether an opening balance has already been entered for the item at the warehouse / bin specified.
Whether the stock item's warehouse / bin is correct.
Whether the stock item code is correct.
All fields are correctly formatted.

What happens when I import the file?

An opening balance transaction is added to the stock history.
The quantity in stock is updated.
The cost price is updated.
The opening balance transaction takes the date from your PC during import.
Nominal postings are made to the stock Suspense Account and the Asset account, if you have enabled Integrate stock management with the nominal ledger (in Stock Control Settings).