Exporting Enquiry Results to Excel
Cadzow 2000 Accounts Receivable and Accounts Payable Enquiries results can be exported to an Excel spreadsheet.
- Click the Accounts Receivable tab.
- Choose Enquiries — Transactions.
- Select the criteria required and choose Next Step (Results).
The enquiry results are displayed.
- Choose the Export tab.
- Choose the export type required.
The two most useful are:
Export - Transactions (basic details, one line per transaction) — this is useful for accounting troubleshooting or auditing; and
Export - Transactions that involve stock (broken down by stock item and analysis) — useful for constructing detailed sales reports.
- Click Export Information.
The Export Data screen is shown.
- Enter a filename and click Export Now.
Note, if the enquiry contains more than 16,000 items, it cannot be exported. This is due to a row limitation in Excel. If the export is too large you will be notified.
Otherwise, the file is opened in Microsoft Excel.
Using Excel Pivot Tables for Analysis
Microsoft Excel has an easy-to-use function called Pivot Tables which allows complex data to be summarised very simply.
The following example builds a sales/purchases summary by month:
- Run an Enquiry with the parameters you desire, for example date, department and so on.
- Export the details as described above and choose Export - Transactions that involve stock (broken down by stock item and analysis).
- Open the file in Microsoft Excel.
- From the Insert menu, choose Columns to insert a column. Repeat three times so there are four blank columns.
- Label these columns Transaction, Month, Value, Spare (ie. in cells A1, B1, C1 and D1).
- In the first cell (A2), enter the following formula:
=IF(E2="Purchase Credit Note","Purchase",IF(E2="Credit Note","Invoice",E2))
- in the second cell (B2), enter the following formula:
- In the third cell (C2), enter the following formula:
=IF(E2="Purchase Credit Note",-S2,IF(E2="Credit Note",-S2,S2))
- From the Data menu, choose PivotTable and PivotChart Report.
- Choose Microsoft Excel list or database and PivotTable and choose Next.
- Choose the data range required (Excel should select the entire sheet), and choose Next.
- Choose New Worksheet and choose Finish.
You will then be presented with a blank pivot table.
- Drag Month onto the Row Fields section.
Drag Transaction onto the Column Fields section.
Drag Value onto the Data section.
The pivot table will then be complete and may be manipulated as required.
For example, if you wish to break the results up into departments, drag Department onto the Row Fields section.
Each data item has a drop-down list which enables you to hide values that are not required; for example, you may wish to remove purchase orders and quotations from the data.
- Experiment with the various results the pivot table can provide.
If you need more sophisticated analysis (say, separating stock into services and products), use formulae in the source data to create the extra information you require, then re-build the pivot table.