AccountingWeb
News
Tutorial: Import data from MYOB into Excel. By David Carter.
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 in Australia for the Apple Macintosh and was one of the first Windows packages in the early 90s. 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 in the UK to compete with the big boys.
But it loses the Description field!
But – for me, anyway – MYOB has one terrible, terrible fault. When you enter a purchase invoice and analyse 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 realised 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 are 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 UK 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 analyse 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, choose: 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.
Continue.
In the next screen the MYOB fields are listed on the right.
Click on the first nine 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.
Minimise MYOB. Start up Excel. Open a blank worksheet.
2. Import the data into Excel
Now select from main menu:
The Select Data Source box appears. Find JOURNAL.TXT and Import/Open it.
The Text Import Wizard box appears - 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). Then click Next.
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. Click 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 and click 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, ie bring new data in from MYOB.
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 – for example 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 (ie 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 nine fields – Journal Number to Exchange Rate.
Unfortunately MYOB doesn’t remember the fields as well. Tick Journal Number to Exchange Rate and Export.
Save the report once again as JOURNAL.TXT (ie 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 select from the top menu bar: Data-Sort: Date-Descending and click 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 summarise 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
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.
Number of comments: 2
AccountingWEB.co.uk 28-Dec-2007
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 6906
I note your comment about removing other currency symbols, especially when dealing with multiple currencies.
I've developed a custom excel function which will do the job:
The function code is as follows:
Public Function RemoveMatch(LookIn, PatternStr, Optional ReplaceWith = "")
' Credited to matthewspatrick on expertsexchange.com
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
With re
.Pattern = PatternStr
.Global = True
End With
RemoveMatch = re.Replace(LookIn, ReplaceWith)
Set re = Nothing
End Function
and it is used as follows:
With amount (for example US$41,038.35) in D2, insert two columns to the right; then in each column type:
E2: =PERSONAL.XLS!RemoveMatch(D2,"[0-9,.,(,)]")
--->results in the currency symbol (US$) or the pound sign (hopefully)
F2: =VALUE(PERSONAL.XLS!RemoveMatch(D2,"[a-z,A-Z]"))
--->results in the value without the currency string (41038.35). Format as required.
(Change row number as required)
(Both these need testing to ensure the pound sign is handled correctly)
This code is best placed in your personal.xls workbook, so that it is available to any other workbook. If you need notes on how to set this up please post back.
You can browse the user-defined functions like any other Excel function to create them and complete the parameters.
This function makes use of regular expressions and pattern matching. It's the first time I've used them for myself, they are powerful things.
In the personal.xls VB editor, under Tools, References, you will need to tick MS VBScript Regular Expressions 5.5.
Hope this is helpful!
Mike James
DataWise Limited
David, thanks for featuring MYOB. It is the number one program for SME's in Australasia by a long way, and after gaining awards in the UK recently, I would anticipate increasing sales in the future.
You've highlighted one of the limitations of MYOB's reporting, in that you cannot see transaction description details on an MYOB (nominal) ledger report.
This particular issue arises from the way in which transactions are posted to the ledger - the amounts are combined by account and job numbers, so there is not always a one-to-one relationship. Hence the use of a memo field which is part of the entire transaction.
We have produced a customised nominal ledger report for MYOB which displays the additional detail.
MYOB will probably not produce their own report-writer in the foreseeable future. For more details of our program, plus trial download and examples of our standard reports, go to www.datawise.co.nz.
Our report writer will work with other country versions of MYOB including the UK.
In addition to the standard reports we have produced many specialised reports for a variety of clients.
Kind regards
Mike James
DataWise Limited
MYOB Reporting Specialists
