AccountingWeb
News
Tutorial: Import data from TAS Books into Excel. By David Carter.
TAS Books contains analysis-ready files for both nominal and sales reporting in Excel. In this first of two tutorials David Carter shows how to get nominal transactions out of TAS and into Excel via Excel’s Data – Import External Data command.TAS Books may not be as snazzy as some of the packages on the market, but I have always found it a very workmanlike and satisfying package. It may not offer everything but what it does, it does pretty well.
When it comes to reporting, TAS offers numerous reports, all of which have on-screen drilldown. So the user who has TAS on his/her desktop is well catered for.
But TAS also offers excellent facilities for exporting data into Excel. You can use them to distribute on-screen reports in Excel to managers who don’t have TAS on their own machines. In addition, you can use Excel to get round a limitation in TAS, that the Job Costing side of TAS doesn’t really work properly.
In this first of two tutorials we will import nominal transactions from TAS into Excel via the Import External Data command.
In the second tutorial we’ll analyse this data with a pivot table and see how you can use Excel to get effective job costing out of TAS.
Tip: Towards the end of this tutorial you will be asked to enter some new transactions into TAS, 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 TAS.
1. Export Nominal Transaction Values from TAS
From main menu, select: Nominal – Reporting – Export Nominal Transaction Values
[We have selected Export Nominal Transaction Values, which gives us the individual transactions. The option below it, Export Nominal Values, exports the monthly balances of each nominal account. Very useful if you want to take the month’s P & L and re-format it in Excel]
Screen 1.38 appears, Export Nominal Transaction details
Make sure that:
Year = Current
Starting Period = 1
Ending Period = 12
(i.e. all the data for this financial year)
In Options, tick: Profit and Loss Type only
At bottom right, tick the Remember button so it is green.
Export.
The Choose file to Export Values to box appears.
Choose the folder you want and make a note of it
Make sure that Save as Type = CSV (*.csv)
Save the file as Ntran01.csv
After the file has been created, TAS asks if you want to view it now? NO
Close down TAS. Start up Excel.
2. Import the data into Excel
In Excel, make sure a new Workbook is open. Click onto cell A1. Then from main menu:
The Import Text File box appears. Find Ntran01.csv and Import it.
The Text Import wizard Step 1 of 3 box appears.
Step 1 of 3. Select: Delimited...Next
Step 2 of 3 Tick Comma - vertical lines appear between the fields....Next
Step 3 of 3 The NL Group field at the left is highlighted.
We DON'T want to import this field. At top left, select Do Not Import Column (SKIP).
The column heading above NL Group changes to SKIP.
Now scroll right to the Sub Type column. (We want to eliminate this too.)
Click on General at the top of the column to highlight it.
Change to SKIP.
Scroll right and eliminate the two columns CR Amt and Dr Amt as well. We already have an Amount field, so they are superfluous.
When all four fields are set to SKIP, select: Finish.
The Import Data box appears. Make sure it is aimed at $A$1.
The transactions are imported into Excel
3. Tidy up the Data in Excel
Make the top Row bold. Freeze panes.
Format Amount (column G ) to 2 places of decimals, negatives in red.
Tidy up any other columns as you see fit.
4. Set the Sort order = Post No - Descending
We want the most recent transactions to appear at the top of the screen. For this, we’ll sort on the Post No field in column J, a sequential number automatically generated by TAS.
Click onto J10 in the Post No column. Click on the ZA icon (i.e sort descending, latest first).
When you have finished, save this workbook as TASImport.xls. Close.
5) Add a new transaction in TAS
The real benefit of using Import External Data comes when new transactions are added to TAS. All you have to do is to press the Refresh button in Excel, and they are added automatically to the TASImport workbook. Therefore:
Add a new transaction (i.e. invoice, payment etc) to TAS Books.
In TAS, go to Nominal - Reporting – Export Nominal Transactions.
The Remember feature at bottom right is green, meaning that the options are the same as last time, so simply run the report and save it once more as Ntran01.csv (i.e. overwriting the existing version).
Close down TAS. Go into Excel.
5. Refresh the data in Excel
Open TASImport.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 appears, with NTran01.csv as the default file name.
Click on Import. The Excel file is refreshed.
The new transactions are included, but unfortunately the report doesn’t come over from TAS in Post No – Descending sort order. We have to set the sort order manually each time we Refresh.
Click ZA on J10 in the Posting No column. The new transactions appear at the top.
You now have a permanent on-screen daybook of nominal transactions that is linked directly to TAS. The only limitations are that:
1) Someone has to run the Ntran report each time in TAS
2) you have to sort into Post No – Descending order yourself after Refresh
In the next tutorial we’ll show how you to analyse this data with pivot tables, and how to add an extra column in Excel to provide proper Job Costing.
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.
AccountingWEB.co.uk 14-Nov-2007
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 4891
by 1 user(s)
