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.
Continued...
The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.
Registration is FREE and allows you to view all content, ask questions, comment and much more.
Or if you are already registered, login here
Over-coming MYOB reporting limitations
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
Excel function to remove currency symbol
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