It is very easy to get nominal transactions out of MYOB and into Excel, although it's a bit of a rocky ride to get them out in the format you want. In this first of two tutorials David Carter shows how to get nominal transactions out of MYOB and into Excel via Excel's Data - Import External Data command.
MYOB (for Mind Your Own Business) is one of the most attractive entry-level packages around. It was originally written for the Apple Macintosh and was one of the first Windows packages in the early 90's. The Mac pedigree makes it very classy and even now it hardly shows its age, but it's never really had sufficient market clout behind it to compete with the big boys. MYOB is now owned by Solution 6, an Australian company, who market it worldwide.
But it loses the Description field!
But - for me, anyway - MYOB has one terrible, terrible fault. When you enter a purchase invoice and analyze each expense to a nominal code, MYOB offers a Description field where you can type in details of what the expense was for. Good.
But when you look at this expense in the Nominal Ledger, MYOB has lost the description! I once ran a charity's accounts on MYOB for a year. At year-end I was looking at my expenses for the year and found I'd lost all the descriptions I'd attached to the costs. This has killed MYOB stone dead for me ever since.
Why do they allow MYOB to ruin the data like this? It goes back to the old days of floppy disk systems when Sales, Purchase and Nominal Ledgers were designed to run as separate modules. A Purchase Ledger was a Purchase Ledger and the nominal analysis part of it was just a bit of icing on the cake.
In those days the MYOB-type design was the norm, with a single description field for the entire invoice. But as hard disks came along and packages became integrated, their designers realized that GL analysis was a vital part of a Purchase Ledger and they had to bring all the GL analysis over into the GL module. So most suppliers gradually corrected their software so that each GL line description came over into the General Ledger, not just the purchase invoice description. Now there's only one or two packages left - like MYOB - with the old style design.
It's a great pity, because MYOB is such a stylish and easy-to-use package. I've mentioned this several times to their guys over the years, but nothing ever happens. But I keep hoping. Who knows? Maybe they'll read this article in Australia.
Export Method #1: Run a report, then use Send To
There are two ways of getting data out of MYOB and into Excel. MYOB has many built-in reports, and whenever you run one of them, the Send To option allows you to export the report into Excel, or output it as a csv or tab delimited file.
Fine, but when you export a report such as Accounts - Accounts Transactions (the nearest you get to a GL Audit Trail report in MYOB) it comes over with all the original formatting. This isn't a great deal of use really. MYOB needs an "export without any formatting" option that just brings the report over as a list of records without any formatting.
Export Method #2: File - Export Data
You can get unformatted data out via MYOB's File - Export Data menu option. In this first of two tutorials we will use the Export Data facility to output an analysis-ready file of transactions as a Tab-delimited file. Then we'll import these transactions into Excel via the Import External Data command.
In the second tutorial we'll analyze this data in Excel with pivot tables and produce some sample reports.
Tip:Towards the end of the tutorial you will be asked to enter some new transactions, 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 MYOB
1. Export Journals Entries
Start up MYOB. From the main menu at top of screen: File - Export Data - Journal Entries
The Export Transaction Journals screen appears:
In the first two boxes, accept Tab Delimited and Header records
In the third box, Source Journal, change Nominal to All
Dated from ..To : enter the beginning and end dates of your current financial year.
In the next screen the MYOB fields are listed on the right.
Click on the first 9 fields Journal Number to Exchange Rate so that they are marked Fields 1-9.
Export. A file is generated called JOURNAL.TXT.
Save JOURNAL.TXT, and make a note of which folder you saved it in.
Minimize MYOB. Start up Excel. Open a blank worksheet.
2. Import the data into Excel
Now select from main menu:
- Excel 2003: Data - Import External Data - Import Data
- Excel 2007: Data - Get External Data - From Text
- Excel 2000 or earlier: Data - Get External Data - Import Data.
The Select Data Source box appears.
Find JOURNAL.TXT and Import/Open it.
The Text Import Wizard box appears - Step 1 of 3
Step 1 of 3. At the top it should say: Delimited (as opposed to Fixed Width).
In the middle it says File Origin 932-Japanese (Shift-JIS), (it does on my machine, anyway.)
From the drop down list, select the file type near the top, Windows(ANSI).
Step 2 of 3. There should be vertical lines between the fields. ...Next
Step 3 of 3. Finish
The Import Data box appears. It should say: $A$1. OK
The transactions are imported into Excel.
3. Tidy up the Data in Excel
First, tidy up Row 1. Make it bold. Then make it left justified.
Now highlight the whole database (click on top left corner). Then: Data - Sort.
Set the sort order to: Date-Descending. OK
This puts the most recent transactions at the top of the screen.
The column headings are making the columns too wide. Narrow the columns to fit the width of the data in them rather than the headings.
Right click for menu. Select: Data Range Properties.
This box controls what will happen when you Refresh, i.e. bring new data in from Exchequer.
In the Data Formatting and Layout section, two boxes are ticked - Adjust Column Width and Preserve Cell Formatting. Untick Adjust Column Width. When you Refresh, the column widths will now stay unchanged. OK.
4. Add two new fields - Amount and NomGroup
A lot of essential data is missing. We have a Nominal Account Code but no Name. There's no customer or supplier code. Instead of Debit and Credit fields we will need a single "Amount" field.
MYOB has also brought over lots of Balance Sheet records - e.g. VAT, Creditors, Debtors and Bank.
These are simply going to get in the way.
We'll start by creating a new field, Amount. Click onto cell J1 (the first blank column to the right)
In J1, type in the heading Amount
In J2, enter the formula =F2-E2 (this makes sales positive, expenses negative).
[If the formula doesn't work, make sure that the Credit and Debit amounts have Â£ signs in front of them. If instead they have square blobs in front, this means you didn't change the file origin from Japanese to Windows-ANSI in section 10.
I also found that amounts prefixed with signs other than Â£ will fail as well. There seems no way of getting round this - it's a fault in MYOB. So if you are running MYOB in multi-currency, calculations on transactions in non-base currency won't work]
Copy this formula down all the rows.
Format the Amount column to 2 places of decimals, negatives in red.
Now in K1, type the column heading NGrp (i.e Nominal Group)
In K2, type the formula =LEFT(D2,1)
This gives the first number of the Nominal Code. Usually 4 = Sales, 5 = Cost of sales, 6 = Overheads
Copy this formula down all the rows.
Right click on the data again for menu. Select: Data Range Properties.
At the bottom, tick: Fill Down formulas in columns adjacent to data.
Just above it, tick Insert Entire Rows for new data, clear unused cells. OK.
Whenever you refresh the data, you will find that these fields will now be calculated for all new transactions.
Finally, save this workbook as MYOB Import.xls. Then Close.
5. Add a new transaction in MYOB
The real benefit of using Import External Data comes when new transactions are added to MYOB. All you have to do is to run the Export Data report, then Refresh in Excel, and the new transactions will be imported automatically. For example:
Go into MYOB. Add a new sales or purchase invoice.
Now go into File - Export Data - Journal Entries.
The previous selections are remembered, so just press Continue.
Tick the first 9 fields - Journal Number to Exchange Rate.
Unfortunately MYOB doesn't remember the fields as well. Tick Journal Number to Exchange Rate.
Save the report once again as JOURNAL.TXT (i.e. overwrite the existing version).
When the report is finished, return to Excel.
6. Refresh the data in Excel
Open MYOB Import.xls. Right click for menu. Refresh Data (at the bottom).
The Import Text file box appears, with JOURNAL.TXT as the default. Import.
The data is imported.
Now highlight all the data. Then: Data - Sort: Date-Descending. OK.
You see the new transactions at the top, and Amount and NGrp have been added to them.
This report is now permanently linked to MYOB. To keep it up to date, periodically run the MYOB export routine, then Refresh Data in Excel and Sort the transactions by date descending. 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 summaries these transactions into any report you want. And whenever you Refresh, these pivot table reports will be automatically updated as well.
We'll create some pivot table reports in part 2.
Save and Close MYOB Import.xls
END OF SESSION
This tutorial originally appeared on our sister site, AccountingWEB.co.uk.