As mentioned in previous updates, OdataLink’s focus for 2023 is to provide standard templates you can download and hook up with your Xero or MYOB data.
It’s no real surprise that the most common requested reports are Profit and Loss and Balance Sheet.
Our focus this month has been to simplify creating these reports.
First up, we have created a tutorial article that explains how to create a standard Profit and Loss Report using Xero data in Power BI.
In the coming days, we will provide a standard Profit and Loss and Balance Sheet template that use and showcase these enhancements.
Continue reading to find out all the recent enhancements made.
Addition of ‘missing’ accounts in the Accounts, ProfitAndLoss and BalanceSheet endpoints for Xero
One of the biggest hurdles when creating Profit and Loss and Balance Sheet reports were that the main endpoints provided values that could not be linked to the chart of account.
The ProfitAndLoss endpoints included a Foreign Currency Gains and Losses row that did not have an AccountID.
The BalanceSheet endpoints included a Current Year Earnings row that included an AccountID that did not exist in the Accounts endpoint.
Both of these factors meant that creating these reports was more difficult. It was not possible to link all rows to the Accounts endpoint.
To resolve this long-standing issue, OdataLink will now do the following:
- Generate an AccountID for these specific rows
- Include a Foreign Currency Gains and Loss account with code of FXGROUPID in the chart of accounts.
- Include a Current Year Earnings account with code of CE in the chart of accounts.
This means that all account-related rows of the ProfitAndLoss and BalanceSheet endpoints can be linked to your chart of accounts.
Note that these changes do not apply to the TrialBalance endpoint. That endpoint already provided values that could be linked to your chart of account:
- Realised Currency Gains and Loss
- Unrealised Currency Gains and Loss
- Bank Revaluations
We acknowledge that this change may break or have an impact on existing integration. Feel free to contact us for advice.
New AccountsTypes and AccountsTotals endpoints for Xero
OdataLink now provides 2 new endpoints for Xero:
These new endpoints provides the data required to calculate values for Gross Profit, Net Profit, Net Assets, etc.
{
"AccountTotal": "GROSSPROFIT",
"AccountTotalName": "Gross Profit / (Loss)",
"IsProfitLoss": true,
"IsBalanceSheet": false,
"IsAccountClassification": false,
"Order": 3,
"AccountsTypes": [
{
"AccountType": "REVENUE",
"DrCrSign": 1
},
{
"AccountType": "SALES",
"DrCrSign": 1
},
{
"AccountType": "DIRECTCOSTS",
"DrCrSign": -1
}
]
},
This, in turn, makes creating Profit and Loss and Balance Sheet reports much easier
Soon, we’ll provide an article that describes how to use these endpoints to create a simple Profit and Loss Report in Power BI and Excel.
New ProfitAndLossMultiPeriodTable endpoint for Xero
Over the years, there was a single question that kept being asked over and over again: “How to pull multiple months worth of Profit and Loss data”.
This is now very simple with the addition of the ProfitAndLossMultiPeriodTable endpoint.
This endpoint accepts three key parameters:
- FromDate
- ToDate
- Period
Using these three parameters, you can get OdataLink to download multiple sets of profit and loss values for Xero.
For instance, you can set the FromDate to the 1st of January 2020, the ToDate to the 31st of December 2023, and the Period to Month and OdataLink will return 48 months worth of data.
Even better, the you can set the Period parameter to:
- Week
- Fortnight
- Month
- Quarter
- Year
This means you can use a single Power Query to download as much or as little Profit and Loss data as you need.
While we started with Profit and Loss, we will add similar endpoints for both Balance Sheet and Trial Balance in the very near future.