Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > API Endpoints >

How to get account totals for date range via API

Started by Bill Pearce -   in API Endpoints

Working on an integration to give users a breakdown of their (expense) accounts for a given financial year.

The Accounts endpoint doesn't seem to have any way to retrieve this information (but is useful in giving me the accounts we might need). The Transactions endpoint doesn't retrieve all transactions, but I think I've worked out I can almost get all the information I need from the Transactions, Invoices, Credit Notes and Payments endpoints all put together. But I'm really just guessing here, and it's horribly inefficient, since there doesn't seem to be a way to filter any of these APIs by account code (since it's nested in the Line Items), so I would literally have to pull all pages of all of these endpoints to get the data I need.

Is Journals a better option? (I'd still then need to get ALL pages of journals to break them down by account and add up the totals? Which could be a crazy amount of data.)

I assume there's a better way to get this information. Any ideas?
Hi Bill - you should be able to filter most every endpoint for a subset of data based on the account Code or GUID.

Ex.
> https://api.xero.com/api.xro/2.0/BankTransactions?where=BankAccount.AccountID==GUID("5a855b6a-1fd4-4fb5-844b-431e6dc9e58c")

> https://api.xero.com/api.xro/2.0/Invoices?where=BankAccount.AccountID==GUID("5a855b6a-1fd4-4fb5-844b-431e6dc9e58c")

However we might need a bit more context on your ultimate goal to guide you in the right direction. Are you looking for the transactions from a single EXPENSE account, or the aggregate data from every possible EXPENSE account?
 

Christopher Knight (Xero Staff)  

Hey Christopher, thanks for the reply!

Just FYI I'm using the xero-ruby client rather than the urls directly.

RE: "We might need a bit more context on your ultimate goal to guide you in the right direction"

We have an app for a particular industry that records users' business expense and gives them various breakdowns and graphs to help them track where they're gaining or losing money and help them run their business better.

Currently they record their expenses manually, for the FY, e.g. "water bills: $1000", "wages: $900" and a couple of dozen other expenses

What we want to do is allow users to authenticate with xero (easy), pull their chart of accounts (easy) and let them match up those accounts with expenses we're recording in our app, and then prefill all these expenses (e.g. wages, water bills) based on the totals in xero for those accounts for the given financial year.

So yup, we're looking for transaction totals for the FY, for all the expense accounts that they select are relevant to us.
 

Bill Pearce  

RE: "you should be able to filter most every endpoint for a subset of data based on the account Code or GUID."

I don't understand Xero's data model nor do I understand accounting at all, but I did try this previously, and found that all the BankAccount data against transactions is the actual Bank Account details, not the expense account. The expense accounts are only ever mentioned in the Line Items of transactions and invoices.

https://gist.github.com/Obversity/746e83812f4b30937715ea02a429c5b1

(This text-editor didn't much like code being pasted into it, sorry!)
 

Bill Pearce  

Ahh - I've just pushed an example route to show what I think you are trying to do!

https://github.com/XeroAPI/xero-ruby-oauth2-app/commit/220e307ee3fd2fce80154d976a60a65a23ab500c#diff-203cc07dcd15a2de00efd1246cdb681ce6c038b10efe0c73d9e3c41273082a1cR75
 

Christopher Knight (Xero Staff)  

Ah, sorry, I haven't been clear. I've had no problem at all using the `get_accounts` endpoint. What I need to be able to do is get the totals, for a given financial year, for those accounts.

For example:

2018-2019:
Advertising: $2340
Entertainment: $5030
Freight & Courier: $233
Rent: $4000
etc

I do appreciate the update to the demo project to use the new syntax though!
 

Bill Pearce  

(If this is something the API can't really do out of the box, but it can be achieved by pulling all the transaction/invoice/credit note/payments data and adding up the totals, that's okay, just let me know if I'll be missing anything using those endpoints!)
 

Bill Pearce