Tutorial: Sage and MSQuery #4 - Add the missing pieces of data. By David Carter

When you bring data out of an accounts package into Excel, key items of data are usually missing. How do you include them? Simple: use Excel's Vlookup function to pull in the extra data from a lookup table. David Carter continues his series on creating an analysis-ready file in Sage Line 50 and Instant.

1. The story so far
In Tutorial 1 we linked up Sage directly to Excel via ODBC. Then in Tutorial 3 we brought over Sage transactions from the Audit Journal into Excel, and from this data created a pivot table report.

To test the live link between Sage and Excel, we entered a new invoice into Sage. Then we went into Excel and pressed the Refresh button. The pivot table report was updated automatically.

This is the key benefit of going into Excel and importing the data, rather than going into Sage and exporting it. Next month, when you want to re-run the report in Excel, all you have to do is press the Refresh button and the report is updated automatically.

2. But key data was missing
In order to create the analysis-ready file we imported most of the contents of the Sage audit trail, taking it from the Audit_Journal data table. However, Audit_Journal omitted certain key items of data which we need for reporting. They were:

• Month Number and Financial Year
  We imported the transaction date, but not the month and year numbers. We could use Excel functions to summarise the figures by month and year (for example Group and Outline, or =MONTH and =YEAR). But these all work on the calendar year, whereas for financial reporting we need to work within the financial year.
• Nominal Categories
  We imported nominal codes and names (eg Audit and Accountancy) but not the nominal categories (eg Finance Costs). So right now we can create a Trial Balance report, but not a Profit and Loss.
• Customer and supplier names
  The customer and supplier account codes came over (eg ACC001), but not the account name (Accounting Supplies Ltd). We can’t expect managers to remember hundreds of account codes; we need to supply them with names.

3. Adding this extra data
This sort of problem is quite normal. After all, Sage is first and foremost a transaction processing system, not a reporting system. One day, when all my dreams have come true, software suppliers will supply an analysis-ready file as standard with their package, but right now we have to sort it out ourselves.

Fortunately, Excel is here to help. In this tutorial we will sort out the first problem – the month numbers and financial year. In the final two tutorials we’ll add the nominal categories and the names.

How? During the last tutorial, you made a "join" between the Audit_Journal table and the Nominal_Ledger table, then pulled the Nominal Ledger Code Name into the report.

We are now going to do exactly the same thing, but this time in Excel. We will make a data table containing the financial month and year. Then we will use Excel’s Vlookup function to join this table and pull the financial year and month into our report.

I’ve already set up the lookup table. Click to download Sage_lookup_table_periods.xls.

4. Contents of lookup table Sage_Lookup_Table_Periods.xls
Start with the dates in column C (DC_Date) and work your way to the right

Column C gives a list of dates, from 01/01/2006 to 31/05/2008

Column D and E gives the equivalent calendar month and year numbers.

Column F gives the period number in the financial year. This company’s financial year starts 1st April, so 1st January 2006 is going to be period 10.

In column G, the financial year for 01/01/2006 is 05_06, and the name of the month in column H is 10_Jan.

[5. Your choice: work with the demo company or your own company?]
These dates are set up for working with a live system and current data. However, in Tutorial 3 we were working with the Sage demonstration company, Stationery and Computer Mart. The dates on those transactions depend on your version of Sage, but they are probably prior to 01/01/2006.

So you now have a choice. You can choose to work on your own company data rather than the demo company. In this case the starting date 01/01/2006 in the lookup table makes sense. However, you will have to re-run Tutorial 3, this time on your own company.

Alternatively, you can keep going with the demo company, and try to adapt the dates in my lookup table to fit those on your version of Stationery and Computer Mart. [or perhaps you could bring your own dates up to 2006 by creating a calculated field NEWDATE, which equals DATE+365 or DATE+730 etc].

Personally, I’d suggest you work with your own company data rather than the demo company. After all, that’s when it really starts to get interesting. And you won’t be able to do any damage to your live Sage data, even if you mess up this tutorial.

So, from now on I’m assuming that you are working on transactions that are dated in 2006 and 2007.

6. Filter out Sage transactions before 01/01/2006
Typically in real life you want transactions for the last 18 months or so. From these you can get this year’s figures plus last year for comparison.

So we’ll now go into MS Query and apply a date filter so that it only brings over transactions dated 01/01/2006 or later.

Open up the Excel file created in the last tutorial – Analysisready.xls

This contains two worksheets, a list of transactions and a pivot table. The transactions list has column headings TRAN, TYPE, DATE, DATENT etc. The transactions are sorted in date order descending, ie the most recent transactions at the top.

Before anything, rename the transaction list worksheet as MASTER, and the pivot table as PIVOT (double click on the tab at the bottom, or right click for menu).

Having renamed the sheets, go into the transaction listing worksheet MASTER. Right click anywhere on it. A menu appears.

Select Edit Query. A message appears “This Query cannot be edited by the Query Wizard”. Don’t worry. Just click on OK. [What this message means, by the way, is that the Query has to be edited in MS Query itself. The Query wizard cannot handle queries that involve more than a single table].

You go into Query, and see the Audit_Journal and Nominal_Ledger tables.

If there is no data in the bottom half of the screen, click onto the left hand Exclamation mark icon to run the Query.

An “Enter Parameter Value” box appears, suggesting 4000. Type in 4000 (ie only P&L accounts). OK. [The two exclamation mark icons correspond to the Records option up in the main menu. See Query Now and Automatic Query at the bottom. You can set to Automatic Query, but if you have a lot of data you might find yourself watching the hourglass for long periods]

From main menu, select Criteria–Add Criteria.

In the Field box, click on the drop down list and select AUDITJOURNAL.DATE (If you can’t read the fieldnames, try right-clicking onto the box. This right justifies the fieldnames).

In the Operator box, change “equals” to “is greater than or equal to”.

Click the Values button at bottom right. From the list, select 2006-01-01, then click OK. Now click on Add.

A new Criteria field appears in the centre of the screen. DATE >=#01-01-2006#

Close. Then click on the left exclamation icon to run the query. Enter 4000.

The query re-runs itself. Now click on the data, then Ctrl-Down Arrow. The transactions at the bottom of the screen should be dated 01-01-2006 or thereabouts.

Ctrl-Up Arrow to get to the top of the screen again.

File–Save the Query (overwriting the old version). Then File–Return Data to Microsoft Excel (at the bottom).

You return to Excel. Click and Control-Down Arrow again. The transactions at the bottom now start from 01-01-2006. We’ve cleared away the old stuff and now have only 2006 and 2007 data.

Return to the top of the worksheet.

7. Insert the Lookup Table into Analysisready.xls
Before doing the lookup, we must implant the Sage_lookup_table_periods.xls into the Analysisready workbook.

Make sure that both Sage_lookup_table_periods and Analysisready are open.

Go into Sage_lookup_table_periods. Right click on the tab name at the bottom, Datelookup.

From the menu: Move or Copy – (at the bottom) tick Create a Copy – (at the top) To Book: Analysisready.xls. Then OK - datelookup now appears in Analysisready.xls, next to PIVOT and MASTER.

You can now close the Sage_lookup_table_periods workbook

8. Use VLOOKUP to pull MthName into MASTER
In the Analysisready workbook, click onto the MASTER worksheet. To the right of DATE, insert two empty columns (columns D and E).

In D1 type MTHNAME. Then click onto D2.

From the menu, select: Insert–Function (or simply click the fx icon).

In Excel 2003, the Insert Function box appears. In the big “Select a Function” box, find VLOOKUP. If it is not there, you can find it in the category: Lookup and Reference.
[Excel 2000 - The Paste Function box appears. Check to see that VLOOKUP is in the right hand list. If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP.]

Highlight VLOOKUP and click OK. The Vlookup box appears, starting with Lookup_Value.

Lookup_Value: Highlight the DATE column by clicking on the C at the top.

Moving lines appear around the DATE column and C:C appears in the Lookup_Value field.

Table_Array: Click the mouse onto the Table-Array field.

Click onto the datelookup worksheet.

Click on the C at the head of the DC_DATE table to highlight the column, then drag to include columns D to H.

Moving lines appear around columns C to H.

We want to insert MthNm in column H, which is 6 columns away from column C. Therefore:

Click back into MASTER. Click onto the Col_Index¬Num field

Col_ Index_Num Type: 6

Range_Lookup Type: false, then click OK

You should see 10_Jan appear in C2.

9. Copy Down to the other rows
Click onto cell C2 and drag the mouse to the right-hand bottom of the cell until it turns into a black cross.

Double click to Fill Down to the bottom of the sheet.

Wait a few seconds (a message saying the percentage completed may appear at bottom left). The column should now be filled with MonthNames.

10. Use VLOOKUP to pull Fin_Yr into MASTER
In cell E1, type FIN_YR.

Now pull the Fin_Yr values into column E in the manner described in steps 8-10 above.

Tip: You are going to have to type in the Col_Index_Num blind. You can work out what the column number is by typing in 1, 2 or 3 etc into the box and seeing the Formula Result at the bottom.

12. Automatically update new records
Finally, we have to amend the Data Range Properties so that, when the Refresh button is pressed, Excel will automatically add MthName and Fin_Yr to any new records.

Right click anywhere in MASTER. From the menu select: Data Range Properties. At the bottom, tick the box: Fill Down Formulas in Columns Adjacent to Data

Immediately above is the option: If the number of rows in the data range changes upon Refresh.

I don’t understand the difference between these three options, but I usually change to the second one – Insert entire rows. This seems to work OK.

If you like, you can now try entering a new transaction into Sage, then running Refresh to see what happens.

In the next tutorial we’ll bring in the Nominal Code Categories as well. Then we’ll use the new fields to create some management reports.

END OF SESSION. Close down and save Analysisready.xls.

Previous tutorials in the Sage & MSQuery series

Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:

Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.