Exporting data from Sage Line 50 into Excel #1. By David Carter
If you want to take Sage data and produce your own reports, you really have only one option - to get it into Excel. The Report Designer in Line 50 is about 15 years old and really a non-starter. Sage has recognised this and has worked hard in recent years to improve integration between Line 50 and Excel.
The key advance was the File'Send to Excel facility introduced in version 8 in 2002. However, there are still a number of traps for the unwary. None is insuperable, but you need to be aware of them in order not to be caught out. Briefly, the main ones are:
1. Dates come over wrongly. They LOOK right, but they are still wrong!
2. Frequently, all amounts come over as positive. For example, an exported list of Sales Invoices will show both Credit Notes and Invoices as positive numbers. So your report totals will be wrong.
3. In some versions, unless you tell it not to, Sage will export deleted transactions as well. So your report totals will include them and be wrong.
4. Essential fields are lacking, especially code names. So, Sage brings over nominal codes, department codes and customer codes, but no code descriptions. For those without a phenomenal memory, this is irritating.
Use these notes to guide you through. Many thanks to AccountingWEB readers for tips and comments, in particular Charles Greenow, Tom Cadogan, Alastair Harris.
PART 1 ' Using File-Send'Contents To Excel to export data
There are two ways of exporting data into Excel from Sage. The first is to export the data as a CSV file via one of the standard reports in the Sage Reports menu. This facility has been in Sage since the beginning.
The second is to display the data on screen, then from the main menu choose File'Send'Contents To Excel. This feature has been available since version 8 and is much more convenient. It also has the advantage of inserting column headings, which the CSV export does not. These notes assume you are using the File'Send option.
Using File'Send to Excel to export a trial balance
You can use the File'Send option to export data from any Enquiry screen in Line 50. You can't use it to export reports produced by the Report Designer and then displayed in print preview.
For example, to export a trial balance into Excel simply:
Click onto Nominal. You see a list of Nominal Accounts together with a debit/credit balance (if you are on version 10, click the Expand All button).
From the main menu select: File'Send'Contents to Excel. That's it. Sage displays a 'copying to Excel' box and bits of paper start floating over.
In version 8 the Copying to Excel box shows how many transactions have been sent and how many are left to go. But in version 10 these have been removed and you now have no idea if the transfer is going to take 30 seconds or 30 minutes. Sage, bring the numbers back please.
After the export is complete, Sage starts up Excel and the TB appears in Excel, with Debit and Credit columns. To check that debits and credits agree, click on the C at the top of the Debit column to highlight it. At bottom right of the screen Excel shows the total. Then click on the D to see if the Credits add up to the same amount.
Other Screens you can export from
Other useful screens from which you can export in this way are:
Limitations of some screens
So, the screen export facilities in Line 50 are good, but there are some limitations. You can't get a list of purchase invoices, for example. And some screens omit key fields. So, if you export a list of transactions for a Nominal account, it doesn't include anything about the supplier.
So on the whole I would suggest that, except maybe for the bank account, don't go to individual screens and export from there. Instead, do it from the 'Financials' screen. This is a little bit more complicated, but it allows you to export all types of transaction, and it contains more fields than any other screen.
PART 2 - Exporting via the Financials screen
The process for exporting from the Financials screen is the same as any other. Go into Financials. You see a list of transactions on the screen.
Exclude Deleted transactions
Generally speaking, you want always to exclude deleted transactions from reports. So before starting to export, first go into the Company'Preferences menu. Make sure that the Exclude Deleted Transactions box is ticked.
Using the Search wizard to filter the data
Usually you will not want to bring over the entire database, but to filter it, that is, bring over only records relating to one account, or this financial year etc.
To filter the data, you need to click on the Search button at bottom left, then choose Wizard. This will help you to generate an 'expression' as Sage call it.
If you have applied filters before, the wizard is a bit clunky, but straightforward enough. If you haven't, there's a worked example in How to Export Sage Data via Search
If you want to select only sales invoices, journals etc, you may need to refer to the list of Transaction Type codes below.
Alternatively, of course, you might find it easier just to export the entire database into Excel and chop out the bits you don't want there.
So go ahead and export the data. Sage will automatically open Excel for you with the data in.
Looking at the data in Excel
Excel should have opened automatically and be displaying the transactions. From the left, Sage bring over the transaction type code, the supplier account code, the nominal code and the department code. In typical Sage fashion, though, they omit all the code descriptions and don't tell you what any of the codes actually mean.
Step 1 Remove all blank rows and columns
Between the column headings and the data Sage has inserted a blank line. This will mess up Autofilter, so remove it.
Also, when you use the Sort function in Excel, it sorts on contiguous data only - any data on the other side of a blank row or column won't be sorted. So get into the habit of removing blank columns and rows. Personally, I would also get rid of columns where there is a column heading but no data. As a general rule, blank columns or rows can so easily cause you grief in Sorts ' get rid of them on sight.
Step 2 ' Check whether Dates really are dates
To the unsuspecting eye, the dates in column G look fine. But in most versions of Sage they are not true dates, but text.
To find out whether they are real dates, see if they sort properly. Click onto G10 in the Date column, then click the AZ icon to sort it ascending.
Now look closely at the column of dates. They are not in true date order, but ascend from 01 to 31!
This is a common problem with Excel export (QuickBooks has the same fault). To check whether a column of dates has come over correctly, click onto the column heading and then the 'comma' icon.
If the dates are real, they should change to look like a column of numbers around 38,000 or so. This is because Excel stores dates not as dates but as numbers, starting from 1st Jan 1900.
If the dates are not real, they won't change but will keep looking like dates. They have to be corrected, as follows:
Step 3 - correct the dates
It is very easy to correct the dates via the Text to Columns option.
Click on the top of the Date column once more to highlight it, then: Data'Text to Columns
Change to Fixed Width'Next-Next in Step 3 at top right select Date DMY. Finish.
Now click on the Comma icon. This time the dates all change to numbers ' they are now true dates.
Thanks to reader Charles Greenow for this invaluable tip. If you use pivot tables, you will know that just one wrong date in 10,000 records is enough to disable the Group function. Personally, whenever I export transactions into Excel, I now run all the Date columns through Text to Columns as a matter of course.
Step 4 ' Add a Month column
Usually, when looking at data you will want to see all the entries for a particular month. To make this easy, you now need to copy the date column and then convert the dates into months. To do this:
Make a copy of the Date column and put it next to the first Date column, so there are two Date columns next to each other. Change the heading of the second column to Month.
Highlight the Month column. Then: Format'Cells
[Excel 1997, Excel 2000 ' click on Dates ' Mar 97]
[Excel 2003, click on Custom ' mmm/yy]
Step 5 ' Use Autofilter to display and total by month
Unclick to remove the highlighting. To see the effect, select: Data'Filter-Autofilter.
Click on the down arrow to the right of the Month column. Choose any month you like. Excel just displays the results for that month.
Now move along to the right to column K, the Net field. Click onto the K. The column is highlighted and at the bottom of the screen Excel gives the total of the column.
Now choose another month on the Month column. Excel recalculates the Sum total each time.
This really is super stuff. I have to say that Excel Autofilter seems to me to be one of the all-time greats of IT design.
Unfortunately, though, the totals are wrong because Sage has exported both debits and credits as positive numbers. You will have to convert one or the other of them to negatives.
Step 6 - Make credits or debits negative
All numbers have come over as positives. So, in the case of a journal for £100 cr and £100 dr, both amounts come over as 100.00. You are going to have to multiply the debits or the credits by -1.
Below is a list of the Transaction Types ('Tp') to be found in Sage.
1 ' SI Sales Invoices
2 - SC Sales Credit
3 - SR Sales Receipt
4 - SA Sales Account (Cash from Customer not allocated)
5 - SD Sales Discount
6 - PI Purchase Invoices
7 - PC Purchase Credit
8 - PP Purchase Payment
9 - PA Purchase Account (Cash to Supplier, not allocated)
10 - PD Purchase Discount
11 - BP Bank Payment (not supplier)
12 - BR Bank Receipt (not customer)
13 - CP Cash Payment (not supplier)
14 - CR Cash Receipt (not customer)
15 - JD Journal Debit
16 - JC - Journal Credit
17 - VP Visa Payment
18 - VR Visa Receipt
Of these, the following are naturally debits and credits (I think):
Credits: BP, CP, PC, PA, PP, SI, PD, VP, JC
Debits: BR, CR, SA, SR, SC, PI, SD, VR, JD
So one of these sets has to be multiplied by '1. Under double entry rules, the credits should be minuses, but management tend to get upset when they see Sales as negative, so maybe make the debits negative instead.
To make the debits (say) negative you can multiply all the numbers by hand or use a Lookup table. We'll discuss Lookup tables in a moment. To multiply by hand, I personally would:
1. Sort the file into Tp order.
2. Make a copy of the Net column to the right of the first Net column. Call it Net2.
4. In the Tp column, choose to show BP or BR.
5. in the Net2 column, insert the formula =J2*-1 (where J2 contains the first Net amount)
6. copy down the formula to all rows by Using the Excel Fill Handle
7. highlight the Net2 column. Edit'Copy, then Edit'Paste Special-Values
Step 7 ' Bring in Missing fields via VLOOKUP
The last problem is that a number of essential fields have not been brought over, principally the descriptions of all the codes. This problem ' that essential fields are left out - occurs all the time with data export. But it's no problem once you know how to use Excel's VLOOKUP function.
For example, the Financials data has the NL code in column D, but it doesn't have the NL description. However, if you export the Trial Balance into Excel, that DOES have the NL description. Using the fact that both files contain the NL code, you can Lookup the code description in the Trial Balance file and pull it into the Financials file.
If you are serious about analysing data in Excel, you have to master the VLOOKUP function. Work your way throught AccountingWeb's tutorial Using Excel's VLOOKUP function to add fields to a worksheet.
A couple of points about VLOOKUP
a) The common field ' Acct Code, Dept code, NL code etc - must be the FIRST column of the table being looked up FROM. (It can be anywhere in the table which the data is being looked up TO.)
b) You can use the VLOOKUP function to convert the debits or credits to negative numbers (step 6). Make a new Excel worksheet, and in column A type in all the Tp codes BP, SI etc. Then create column B next to them and insert 1 or '1 against each one. Use VLOOKUP to pull in the value 1 or '1 and multiply it by Net. Thanks to Alastair Harris for this suggestion. For a discussion among the experts of ways to convert the positives to negatives, see Excel Experts wanted urgently. Particular thanks to Philip Hodgson of VT Software who has made available an Excel function to multiply Sage credits for AW readers.
David Carter, Consultant Editor