Powering Up MYOB’s New Foreign Currency functionality(Part 2)

In my previous blog, I reviewed MYOB’s initial release of the foreign exchange routines for their flagship product, AccountRight 2018.3.

I covered the setup and configuration of a foreign bank account and the transaction flow of a foreign spend money transaction as it appears in your chart of accounts. Its was a big change to the original method provided in the v19 “Classic” product but a long-overdue improvement and simplification of their overall master chart of accounts.

Almost a year later foreign currency has now extended to Customers and Suppliers so let’s examine how the new currency solution works with the MYOB Customer ledger on version 2019.3 and 2019.4.

Essentially MYOB has removed the exchange account for your debtor control account and all amounts are shown in the local currency of $AUD. A great simplification however reporting on the originating currency transaction values still a work in progress as I write this blog. I do like that MYOB has surveyed its customers for the most important reports and feature missing they want which is promising. I’m sure clients will be looking forward to the improvements in the coming releases.

Initial results for the foreign currency solution are promising and I’ve had many client queries about how this functionality can work with Microsoft Power BI desktop.

With this latest release, version 2019.4 users now have access to the updated API to access customer foreign currency endpoints.

Here’s a brief review of the new features and how you can use Power BI to get a better reporting experience on your currency transactions.

As documented in MYOB’s AccountRight v2 API, the foreign exchange currency endpoints are shown at the Sales Invoice “Control” & “Sub Ledger” levels.
https://developer.myob.com/api/accountright/v2/sale/invoice/

The API will now allow users to interact with the customer sales ledgers to output the currency records and reveal exchange rates, default customer currencies and foreign currency amounts. These new API features provide Excel or Power BI Desktop users with the ability to query the data and output the sales ledger table data.

MYOB version 2019.3 currently supports foreign currency endpoints for both item and service customer invoice layouts. Professional and miscellaneous invoice layouts don’t support foreign currency endpoints at the time of writing this blog. Version 2019.4 adds the supplier side and also some other foreign currency key fields.

As I mentioned previously, the new foreign currency functionality has been re-designed and operates completely differently to the “Classic v19” product. The new configuration is definitely a major improvement and really aligns well against current best practices surrounding foreign currency accounting concepts.

I have found, however, that MYOB clients are still requesting additional foreign currency reporting options which need improvement and will hopefully be coming in future releases. MYOB has outlined on the support portal what functionality is currently not available for users. Here is the link to that support reference.

As I demonstrate below, Microsoft’s Power BI Desktop is a free software application that can be used to bridge reporting gaps in systems and provides a fantastic alternative for management reporting.

Before you start, there are a couple of pre-requisites required for your MYOB reporting solution. You need to install the latest version of Power BI Desktop, MYOB AccountRight 2019.3, MYOB API and of course a copy of your datafile.

Next, you need to open a web browser (on MYOB pc) and enter the localhost web URL shown below to retrieve your data file information.

Note the ID shown in the image below (Client User ID) will be required by Power BI to work out which MYOB data file you want to query. If you cant get to this point means your API software is not installed or configured properly and you need to troubleshoot the installation of the API.

MYOB API
http://localhost:8080/AccountRight/

To start your Power Query, open Power BI desktop and enter a blank query by clicking the “HOME” tab from the top menu selection and “EDIT QUERIES“.

Note the November 2019 release changed the Power BI ribbon design to be more in line with Office 365 hence the EDIT QUERIES is now called TRANSFORM DATA

Select the “NEW SOURCE” button and choose a “New Blank Query“. This opens the blank query in the editor.

Then click the “Advanced Editor” button and paste my query below, note you will need to change the ID to the value returned from your own API shown in the web browser.

let
Source = Json.Document(
Web.Contents(
“http://localhost:8080/AccountRight/73d88ab3-d697-475e-aa0c-0c99677edef0/Sale/Invoice?api-version=v2″))
in
Source

Click the “OK” button and it will return the initial output shown below. You can see the end point’s metadata returns a list of 62 items and there are no more pages shown as the Next Page Link is null. If you have further data shown in the next page link you will need to perform additional steps which are not covered in this blog.

Sales Invoice Output

Clicking on the yellow coloured “List” (shown above in the green highlight) will perform the “Navigation” step in the “Applied Steps” panel shown in the image below on the right.

This action reveals a list of records containing the columns and rows of sales data.

I then complete some basic power query transformations such as converting the list to a table, expanding the sales records, choosing column names and filtering the required data.

Navigating to List of Records

After performing these steps you should be presented with the sales customer ledger data as shown below.

Extracted Customer Sales Ledger with Foreign Currency data shown

I have just selected a few columns for my report. Note each of the key values for reporting as follows:
1. The Total Local Amount of the invoice converted to the local AUD currency.
2. The Total Foreign Amount based on the chosen currency
3. The currency code of the transactions.
4. The exchange rate based on the currency chosen and exchange rate entered into the invoice.

Now that you have your key data columns it’s possible to format these values and build measures to break out the currency data and various business metrics required by management.

Here is an example of my Customer Sales Ledger Model and a custom foreign exchange report I have built using MYOB’s demo file, Clearwater.

Using Chris Webb’s blog explained here, I have added some base64 images for a customer logo, country flag, currency symbol slicer.

This really adds a dynamic impact to reports and saves valuable model size with the improved image techniques so thanks to again to Chris and Jason Thomas and Gerhard Brueckl for the initial post offerings on this subject matter.

master-customer-ledger-forexblog-1

 

Power BI Desktop really adds a powerful dimension to your business reporting systems so I would encourage all accountants out there to give it a try!

It’s free to download and use, you only have to pay a subscription to Microsoft if you want to share your reports with others or automate and schedule the data refreshing capabilities.

Look out for my future blog posts where I will examine foreign currency hedge contracts and further currency endpoints as they are released by the MYOB.

Recommended Posts
Showing 7 comments
  • Steven Doherty
    Reply

    Hi Gary,
    I’m hoping you can help me. I’ve been working on behalf of client that is using V2020.2 of the MYOB Local API connector. The documentation now indicates an authorization header and version requires to be passed. I’ve created an M Query like this to pass the details as parameters
    Source = Json.Document(
    Web.Contents(“http://localhost:8080/AccountRight/” & #”API Key” & “/Sale/Invoice”,
    [
    Headers=
    [#”x-myobapi-cftoken”= Base64_Token ,
    #”x-myobapi-version” = v2]
    ]
    )
    )
    in
    Source
    However, I keep getting an access denied error. Are there other elements required to gain the connection? Is connection to Power BI even still possible?

  • Garry Alweyn
    Reply

    Yes Power BI Desktop (July 2020 Release) does work with MYOB Account Right 2020.

    The documentation from the MYOB developer team advise different ways to connect to your data depending where data is stored. Ie Local machine, network location or MYOB’s cloud location.

    When querying a local host you have shown in your comment, you don’t need a header or API key.

    Just replace API Key with the CUID (Client ID of MYOB file loaded to your MYOB library) as shown in my example above.

    Note the CUID can change over time due to upgrades or restore processes.

    Hope that helps and good luck!

  • Sangay
    Reply

    Hi Garry,
    I had been lost for almost a month surfing on internet to connect MYOB AccountRIght Plus 2020 version to POWER BI and finally came to your blog which was like light in the dark. I could connect to MYOB but the List Came as rows of http link rather data. I can provide a picture along.

    • Garry Alweyn
      Reply

      Leave a copy of your your m-code so i can see how your are requesting the data.

      • Sangay
        Reply

        Hi Garry,

        I have put this in the editor

        let
        Source = Json.Document(
        Web.Contents(
        “http://localhost:8080/AccountRight/API key “)),

        in
        Source

        I get :
        CompanyFile:Record
        Resources:List

        When I click on List:
        1. http://localhost:8080//AcountRight/API Key /Account/
        2. http://localhost:8080//AcountRight/API Key /Employee/
        and the list goes on

        • Garry Alweyn
          Reply

          So replace the API key with the CUID as I have shown in my example above. The API Key is for online files on MYOB’s cloud.

          You can obtain the CUID from the local host browser snap shot shown above. You need api version number and format command which is probably what you are missing.

          You can then update the URL to include the endpoint you want ie accounts table or employee list etc.

          Good luck and hope that helps with your Power BI journey.

          • Sangay

            Thank you Garry for the reply. Helped me alot 😀

Leave a Comment

Start typing and press Enter to search

CustomerInsights