API extraction to SQL Data Base

[Andrew999]

Can I get some information on how to extract data from Myob online into an SQL data Base to import into a corporate reporting software model, or is it possible to create a direct link.

[Haydes]

Hi @Andrew999 

Not sure if you're referring to AccountRight or Essentials here but there are API endpoints for both that you can send GET requests to in order to fetch and return data to your application.

You can find the documentation for the endpoints here: https://developer.myob.com

Once you have a working application that gets the data from the endpoints you'll just need to whip up something that then posts the returned data into your DB.

[Andrew999]

Thanks Hayden,

I will go through this documentation. 

Best Regards

Andrew

[Andrew999]

Hello Hayden,

I am trying to extract data from Online Accountright directly to excel using power query 

I have my API key but as I am not a developer it is a bit difficult. If you can give me a few steps to guide me to the data I would appreciate it. 

I use: 

https://api.myob.com/accountright

and my key but get an error " a web API key can only be specified when a web API key name is provided. Help please!!

Then trying to understand the "Advanced "tab in excel PowerQuery "from Web" what do I fill in on URL parts, URL preview, command time out and HTTP request header parameters. If you can give me this as an example to access the API Sandbox demo that would be extremely helpful. 

Thank you.

[MAC221267]

Did you find a solution to this? 

Trying to do the same. Feels like MYOB want everyone to purchase Add-ons instead of providing an easy solution to replace ODBC for Excel users.

I feel its against Trade Practices Act to provide new software that no longer performs the same tasks as the old software you paid for. Instead making you pay for Add-ons

Cheers Mark

[Mike_James]

Hi @MAC221267 , @Andrew999 , when AccountRight Live was first introduced, MYOB attempted to provide an ODBC equivalent, but they realised that it had significant limitations, and developed the API instead, which is far superior.

To extract data to Excel from a cloud file, whatever process or code you use needs to be able to authenticate via oauth2, and navigate the 2FA requirement. 

An easier approach is to make a backup of your cloud file, and restore it to your local library, where all you will need is a username and password.

For example, these are the URL parts of my data source which returns a list of all suppliers:

http://localhost:8080/AccountRight/<your file id>/Contact/Supplier 

?x-myobapi-version="v2"

&Authorisation="Basic QWRtaW5pc3RyYXRvcjo="

Notes:

FileID can be found by entering http://localhost:8080/AccountRight/ into your browser and search for your company file

The second part starts with a ?

The third (and subsequent, if any) parts starts with a &

The phrase QWRtaW5pc3RyYXRvcjo= is the encoded form of <username>=<password>, in this case Administrator=, you can get this from base64encode.org.

I specify the file to be opened as json.

The full text of my query in Excel 2016 is as follows:

let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/<my file id>/Contact/Supplier" & "?x-myobapi-version=""v2""" & "&Authorisation=""Basic QWRtaW5pc3RyYXRvcjo=""")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"ABN", "ABNBranch", "CreditLimit", "VolumeDiscount", "ExpenseAccountId", "UseSupplierTaxCode", "Memo", "PurchaseLayoutId", "HourlyBillingRate", "PrintedForm", "PurchaseComment", "ShippingMethod", "TaxIdNumber", "TermsId", "TaxCodeId", "FreightTaxCodeId", "CostPerHour", "IsReportable", "CoLastName", "FirstName", "IsIndividual", "EnteredId", "IsActive", "Addresses", "Description", "Tags", "CustomLists", "CustomFields", "CurrentBalance", "Uri", "Id", "RowVersion"}, {"Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.CoLastName", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Addresses", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.Id", "Column1.RowVersion"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"Column1.CoLastName", "Column1.Addresses", "Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.Id", "Column1.RowVersion"}),
#"Expanded Column1.Addresses" = Table.ExpandListColumn(#"Reordered Columns", "Column1.Addresses"),
#"Expanded Column1.Addresses1" = Table.ExpandRecordColumn(#"Expanded Column1.Addresses", "Column1.Addresses", {"Index", "Street", "City", "State", "PostCode", "Country", "Phone1", "Email"}, {"Column1.Addresses.Index", "Column1.Addresses.Street", "Column1.Addresses.City", "Column1.Addresses.State", "Column1.Addresses.PostCode", "Column1.Addresses.Country", "Column1.Addresses.Phone1", "Column1.Addresses.Email"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1.Addresses1", each ([Column1.Addresses.Index] = 1)),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"Column1.CoLastName", "Column1.Id", "Column1.Addresses.Index", "Column1.Addresses.Street", "Column1.Addresses.City", "Column1.Addresses.State", "Column1.Addresses.PostCode", "Column1.Addresses.Country", "Column1.Addresses.Phone1", "Column1.Addresses.Email", "Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.RowVersion"})
in
#"Reordered Columns1"

The query expands various lists eg addresses, and filters on address 1. 

I hope this is enough to get you started. I don't do much reporting directly into Excel from AccountRight Live as I have our own excellent report writer to work with .

[MAC221267]

Hi Mike,

After spending 3 days on this, the following code is that I needed to access my Company file through Excel Power Query Advanced Editor:

let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/3a57d5e8-88b0-44c1-b3d8-4d59ad327a5c/Account/?api-version=v2&format=json")),
// Data source for Item Ledger and conversion to Table

ToTable = Record.ToTable(Source),

// Output number of records in Count and drill down on Value
CountItems = Source[Count],

// Bring Back Source Step
SourceBack = Source,

// Expand list if source count is not null otherwise dont complete next steps
CountTest = if Source[Count] = null then null else Source[Items],
NullTableCheck = if CountTest = null then null else Table.FromRecords( CountTest)
in
NullTableCheck

I can then do all other data maninulation in the Excel Power Query Advanced Editor which then adds it to the code like below for example:

let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/155359a9-e182-4563-90e4-29263e29f3b9/Account/?api-version=v2&format=json")),
// Data source for Item Ledger and conversion to Table

ToTable = Record.ToTable(Source),

// Output number of records in Count and drill down on Value
CountItems = Source[Count],

// Bring Back Source Step
SourceBack = Source,

// Expand list if source count is not null otherwise dont complete next steps
CountTest = if Source[Count] = null then null else Source[Items],
NullTableCheck = if CountTest = null then null else Table.FromRecords( CountTest),

// Data Rows and Columns Sorted
#"Removed Other Columns" = Table.SelectColumns(NullTableCheck,{"Name", "DisplayID", "CurrentBalance"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"DisplayID", "Name", "CurrentBalance"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"DisplayID", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([DisplayID] = "2-2130"))
in
#"Filtered Rows"

I realy don't understand why MYOB wouldn't just realease a 2 page document explaining this to everyone instead of directing them to third party addon's wanting $50/month on top of the $100 plus we are already paying you.

Pleaese are you able to add the additional code to the aboove so I can access my onlline company file as well.

Otherwise I will have to spend another 2 days working it out myself?

This is really poor of MYOB not to provide this info considering how easy it would be to.

[Mike_James]

Hi @MAC221267 thanks for posting your example.  I don't have a solution for an online file,  sorry to say.  Maybe someone can help us out, if indeed it can be done. 

[Andrew999]

Hello Mark & Mike

I think the issue is the authentication it is to achieve without a manual and the right support. 

I agree with your earlier comments, Myob don't understand the customer experience is so important. I am finding with some of my clients, Xero is taking over at a rapid rate, recommended by Tax accountants and in some cases not appropriate for the business if they do not have other ERP systems. The frustration levels are immense, and they have and will continue to lose market share through this arrongance. 

Cheers Andrew