AccountingWeb

Tutorial: Import data from Iris Exchequer into Excel. Part 1. By David Carter

Data in Iris Exchequer is very well organised. There’s an excellent data dictionary, which means that field names are comprehensible and there’s no need to make joins between data tables.

Exchequer is a “single ledger” design, so that all the different transaction types (purchase invoices, sales orders, time sheets, etc) are all stored together within one massive table, or rather two – Document Header and Document Details.

Because these two contain around a dozen different transaction types, they seem pretty big and complex at first. But if you are serious about reporting, believe me, this is the way you want your data to be organised.

The Report Writer module
Exchequer is very good at getting master file data and monthly balances into Excel via "entfuncs" (Enterprise Excel functions). But with transaction data it's surprisingly poor. For example, you can’t export the transactions on an Exchequer ledger screen into Excel.

You CAN output an Exchequer report such as the Audit Trail to Excel, but it comes over formatted just like the printed report. This is fairly useless really.

If you want to get transactions out of Exchequer and into Excel, the only realistic way is via the Report Writer.

Fortunately, it's very easy since the Exchequer Report Writer is excellent. (I'm referring here to the original Report Writer, not the recently introduced Visual Report Writer which is nowhere near as good).

In this first of two tutorials we will use the Report Writer to create an analysis-ready file of transactions to create GL and Cost Centre reports in Excel. Then we’ll import these transactions into Excel via the Import External Data command.

In the second tutorial we’ll analyse this data in Excel with pivot tables and produce some sample reports.

Tip: Towards the end of this tutorial you will be asked to enter some new transactions into Exchequer, then import them automatically into Excel. So before you start, get hold of a couple of purchase invoices that aren’t on the system yet and need to be posted onto Exchequer.

1. Create the report in Report Writer
Start up Exchequer. From main menu, select: Reports–Report Writer (not the Visual Report Writer)

You are in the green Report Tree screen.

Add. The Add Report Record screen appears. Fill it in as follows:

Name: GLIMPORT

Type: Line Based Report

Desc: GL Transaction Import into Excel

Main File: Document Details

Data Search Index: Document Period

Link to Input Line: Change 0 to 1

Output To: CSV file

OK.

2. Enter the Report Fields
At the centre top of the screen, click on the “Report Lines” tab.

The screen is now light blue. Add.

The Add Report Field screen appears, with the cursor in the Field Code box. Fill it in as follows:

Field Code: THFOLIO

Ignore all the other boxes. At the bottom, click OK.

Now click on Add again and enter these fields as well.

THDATE
THPERIOD
THOURREF
THACC
ACCOMP
TLDESC
TLNETBAS
TLCC
TLCCDESC
TLNOMCOD
NMDESC
NMGROUP
NMTYPE

At the end you should see 14 fields, numbered R000001 to R000014

3. Apply Sort order
We are going to apply a sort order of THFOLIO – Descending. [This will print the most recent transactions first. THFOLIO is a sequential number automatically assigned by Exchequer.]

Highlight the first line R000001 Folio Num. Edit.

Click onto the Field Setup tab.

Click into the Sort Order box.

Type in 1D (number 1, not letter I).

OK.

4. Apply Filter – only accounting transactions
The Document Detail file contains all the transaction types including orders, stock adjustments, timesheets etc. But we only want accounting transactions.

Exchequer assigns folio numbers to accounting transactions only. So we will set up a filter “Folio number must be greater than 0”.

Highlight line R000001, Folio Num, again.

Edit. Field Setup.

In the Record box (bottom but one), type: R1 > 0 [one space either side of the > sign]

OK.

5. Apply Filter – no zero value records
There may also be unwanted comment lines (delivery note instructions and numbers etc). To get rid of them we’ll apply another filter: “Amount field must NOT be zero”.

Highlight R000008 Net Total. Edit. Field Setup.

In the Record box, type: R8 <> 0 (single space either side of <>). OK.

6. Apply Filter – only Profit and Loss-related records
Nominal account records in Exchequer are of three types – Header, Balance Sheet and P&L (H, B and A). We are only interested in Profit and Loss-type transactions, type A. Therefore:

Highlight field R000014 T (bottom of the list). Edit. Field Setup.

Record: R14 = “A” ....(text values have to be surrounded by inverted commas. don't forget your spaces.)

Finally, we don’t actually need this A value to be printed in the report. So go the Print Field box an inch or so above and remove the tick. OK.

7. Apply Filter – only this year’s transactions
We only want to bring over transactions for this financial year. Therefore:

Click onto the “Input Lines” tab at the top. Add.

The Add Report Record screen appears. Fill in as follows:

Description: Period From……To……….

Type: change Date to Period

Period: 01/2007 to 12/2007 (ie anything in the financial year 2007)

OK. Close.

The report is complete. You are now back at the green Report Tree screen.

8. Run the Report
Now run the report. Highlight it, then click the Print button.

The Input Fields box appears, periods 01/2007 to 12/2007. OK to accept.

The Print CSV Report To box appears.

Choose the folder you want and make a note of it

File name: GLIMPORT Save. (Save as type should say CSV Files)

The Object Thread Controller box appears. It says something like:

Processing Report 0 / 999999

Meaning that there 99999 records on the system and it has chosen none of them.

Keep your eye on the 0. It will increase as records are selected.

When the report is completed, a blank Print Preview screen appears. Total Records = 0.

Don’t worry; it HAS worked. Close the screen (diagonal white cross on green).

Now start up Excel .

9. Create the Headings.xls and Exchequer Import.xls workbooks
When the Report Writer creates a CSV (“Comma Separated Variable”) file it loses the column headings. Rather than type them in each time, we’ll store them in a separate workbook.

Open a new workbook. Across Row 1 type in these column headings.

Folio (in cell A1)
Date (in cell B1)
Pd (in cell C1) etc etc.
Our Ref
AcctNo
AcctName
Details
Amount
CC
CCName
GLCode
GLName
GLGroup

The last entry GLGroup should be in cell M1.

Make this row of headings Bold.

Save the workbook as Headings.xls.

Save it again, this time as Exchequer Import.xls

Click onto cell A2.

10. Import the data into Excel
Now select from main menu:

Find GLIMPORT.CSV and Import/Open it.

The Text Import Wizard box appears - Step 1 of 3

Step 1 of 3: Select: Delimited... Next.

Step 2 of 3: Tick the Comma box. Vertical lines appear between the fields....Next

Step 3 of 3: In column 2, dates are displayed in the format 20071129. To correct this:

At the top of the column, click on General to highlight all the dates.

At the top of the screen, change the column data format from General to Date YMD.

Finish.

The Import Data box appears. It should say: $A$2. OK.

The transactions are imported into Excel, starting at Row 2.

Check that there are 13 columns of data matching to 13 column headings.

11. Tidy up the Data in Excel
Note that the folio numbers in column A are in descending order, with latest transactions at the top.

Format the Amount column H to 2 places of decimals, negatives in red.

Tidy up any other columns as you see fit.

When you have finished, save and close Exchequer Import.xls.

12. Add a new transaction in Exchequer
The real benefit of using Import External Data comes when new transactions are added to Exchequer. All you have to do is to press the Refresh button in Excel, and they will be added automatically. For example:

Go into Exchequer. Add a new sales or purchase invoice. (Note: it doesn’t have to go through the Daybook Update).

Go into the Report Writer and find GL Transaction Import into Excel.

Highlight it. Print.OK.

Save the report once again as GLIMPORT.CSV (ie overwrite the existing version).

When the report is finished, close down and go into Excel.

13. Refresh the data in Excel
Open Exchequer Import.xls

Right click anywhere on the data. At the bottom of the menu, click on Refresh Data.

The Refresh Data box appears. OK.

The Import Text file box appears, with GLIMPORT.CSV as the default file name.

Import. The Excel file is refreshed and new transactions appear at the top of the screen.

This report is now permanently linked to Exchequer. To keep it up to date, periodically run the Exchequer report and Refresh. The latest transactions will appear at the top of the screen.

This is valuable in itself, but you can also use the power of Excel pivot tables to summarise these transactions into any report you want. Whenever you Refresh, these pivot table reports will be automatically updated as well.

We’ll create some pivot table reports in part two next week.

Save and Close Exchequer Import.xls

END OF SESSION

Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Reporting tutorials index: Use MS Query to extract and analyse accounts data
Reporting Tools #4: Introduction to summary reporting
Towards an analysis-ready file for reporting #1
Towards an analysis-ready file for reporting #2
Management Reporting in Sage Line 50 and Instant
Want to learn more about pivot tables? Start here
Improve your reporting skills with self-teach tutorials
An introduction to Excel-driven reporting tools

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.