AccountingWeb

Tutorial: Import data from MYOB into Excel. Part 2. By David Carter.

In the first tutorial we imported data from MYOB using Excel’s Import External Data command. We also added a couple of calculated fields, Amount and NGrp (Nominal Group).

However, if we want to create reports there’s still a lot of data missing. MYOB has lost the line description. And while we have code numbers for the nominal account and nominal group, we really need their names as well.

The lack of line description is a design fault within MYOB and we can’t retrieve it. [But Mike James of New Zealand MYOB specialists Datawise has developed a report which includes it].

However, it will be easy to add the nominal account and group names via a lookup table, so we’ll start with that.

1. Create a Nominal Accounts Lookup Table
First, start up Excel. Open MYOB Import.xls, the file you created last time.

Rename the worksheet containing the data as MASTER. Delete any empty worksheets.

Now start up MYOB. From the main menu at top of screen: File – Export Data – Accounts

The first screen shows a row of months. Just choose: Continue

In the next screen the MYOB fields are listed on the right.

Select these fields: Account Number, Account Name, Header, Account Type

Export. A file is generated called ACCOUNTS.TXT.

Save ACCOUNTS.TXT, and make a note of which folder you saved it in.

Minimise MYOB.

In Excel, open ACCOUNTS.TXT (to see it, change Files of Type at the bottom to All Files)

The Text Import Wizard appears. Click on: Next – Next - Finish.

You see Account Number in column A, Account Name in B, and in column D Account Type (Assets etc).

We will now move this into MYOB Import.xls.

At the bottom of the worksheet, right click on the ACCOUNTS tab.

From the menu, select: Move or Copy. Tick the Copy box at bottom.

Now at the top, click on To Book. From the list, select MYOB Import.xls. OK

In MYOB Import.xls a new worksheet ACCOUNTS appears next to MASTER.

Rename this worksheet as : Lookup. Then Save.

Close down ACCOUNT.TXT. Don’t Save

2. Pull Account Name and Account Type into MASTER
Now click onto the MASTER worksheet

The first empty columns to the right are columns L and M.

In L1 type the heading AcctName. In M1 type AcctType

Click onto L2.

From the menu, select: Insert – Function (or simply click the fx icon).

[Excel 2003] The Insert Function box appears. In the big “Select a Function” box, find VLOOKUP
(If it is not there, you can find it in the category: Lookup and Reference)

[Excel 2000] The Paste Function box appears. Check to see that VLOOKUP is in the right hand list.
(If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP)

Highlight VLOOKUP and click OK. The Vlookup box appears, starting with Lookup_Value.

Highlight column D, Account Number, by clicking on the D at the top.

Moving lines appear around the “Account Number” column and D:D appears in the Lookup_Value field.

Click the mouse onto the Table-Array box.

Now click onto the Lookup worksheet.

Click on the A at the top of column A, then drag to include columns B to D

Moving lines appear around columns A to D.

Click onto the Col_Index-Num box. Type…: 2 (i.e. the second column)

In the Range_Lookup box, type…: false OK

You should see the Account Name appear in L2. (Mine says Trade Debtors)

Copy down this formula to all rows. (if you see some #N/A signs, don’t worry)

Now fill in the AcctType column M in the same way. (this will be column number 4)

3. Calculate Month and Year numbers
We need to produce monthly totals in the reports, but MYOB doesn’t bring in a month number.

MYOB does bring in the date, and normally, it would be possible in a pivot table to summarise dates by month using Excel’s Group command. However, when MYOB imports journals it leaves a blank line between each one. And any blank Date values will cause the Group command to fail.

So, we’ll have to add two more calculated fields - for MONTH and for YEAR.

In N1 type the heading Mth. In O1 type Yr.

The Date field is in column B. Therefore:

In N2 type =MONTH(B2)

In O2, type =YEAR (B2)

You should see Month and Year numbers.

Copy both formulas down to the bottom row.

[These are Calendar month and year. If you would like to have Financial Period and Year, you need to insert another Lookup table similar the Nominal lookup you’ve just done. Click on Pivot Tip #31 to see how.]

The six columns J to O are all calculated values.

If you remember, during the first tutorial we right clicked on Column D and from the menu selected Data Range Properties. Do this now, and at the bottom makes sure that the “Fill Down Formulas in columns adjacent to data” box is ticked. (in Excel 2007: Table – Data Range Properties)]

Whenever you press the Refresh button, Excel will automatically calculate these six fields and add them to any new records.

4. Create the pivot table
We now have sufficient data to create a Profit and Loss report with a pivot table.

[I’m assuming you have done pivot tables before. If you need a refresher, practise on the first couple of Five Minute Pivot Table tips .]

Click anywhere on the Amount column in the MASTER worksheet. From the main menu at the top of the screen, select :

Data - Pivot Table and Chart Report
[These instructions are based on Excel 2003. Excel 2007: Insert – Pivot table.]

Step 1: Next Step 2. Next

Step 3: Click on Layout. The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet.

From the list, drag and drop into the boxes as follows:

ROW………. NGrp, Acct Type, Account Number, Acct Name
COLUMN…. Mth
PAGE……… .Yr
DATA…….….Amount

[When dropped into the DATA area, Amount should say “Sum of Amount”. If it says “Count of Amount”, double click on it and change Count to Sum]

OK. Finish. The Pivot Table is created in a new worksheet.

You see the Nominal account headings down the left, with month names across the top – a standard P&L layout.

But it needs to be tidied up a bit.

5. Tidy up the pivot table
Excel adds subtotals to each Row item. These need to be removed.

Double left click on the grey Account Number field button in C4.

In the Pivot Table Field box, change Subtotals at the left from Automatic to None.

In the same way, remove the subtotals from Acct Type in B4

Now click on the down arrow in B1. Select the year you want to show.

Now re-format all the numbers to zero decimal places.

Finally, to show just the P&L, click onto the down arrow in the NGrp field A4. Untick 1 and 2.

6. 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.

Unfortunately MYOB doesn’t remember the fields as well. Tick Journal Number to Exchange Rate.

Export.

Save the report once again as JOURNAL.TXT (i.e. overwrite the existing version).

When the report is finished, return to Excel.

7. 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 etc have been added to them.

Now go into the pivot table. Right click for menu. Refresh. The pivot table is updated as well.

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.

Print Friendly Mail to Friend Post Comment