Xero import using Tracking feature?

XeroTrackingMappingImport Assumptions
13 posts / 0 new
Last post
Kevin Ballinger A+ 8
Xero import using Tracking feature?


Has anybody managed to capture the Tracking feature within Xero to integrate with Modano's mapping facility? I'm referring to Xero's relatively limited feature whereby a Tracking Category can be defined, say "Sales Region", with associated Tracking Options of "Northern Division", "Southern Division, "Western Division" - at the Sales/Invoice stage within Xero this is selected and thereafter is a reportable element. This would be fabulous to harness withtin Modano's mapping from Xero....

Anybody have success with this? Or is this one for the "list of design requests"....!



Michael Hutchens A+ 189

Hi Kevin,

Modano is accounting package agnostic, we've just added some basic integration with Xero and QuickBooks because they both have APIs which make this quite easy to do. But it's not a primary focus, as the they're both just historical data sources and there's really only marginal benefit from using the APIs rather than simply exporting to Excel and then importing from the exported Excel file.

I'm not a Xero specialist, but I'm assuming that the Tracking feature within Xero is really just the ability to categorise your financial statements data into groups (e.g. regions, business lines, etc.), and that this is often how Modano users would want their historical data from Xero to appear in their Modano model. Let me know if I'm missing something here.

If this understanding is correct, I'd recommend using the following approach to import from Xero including your financial statements including the Tracking feature improvements:

  1. Open the most suitable dynamic template via the Modano tab, New menu, Dynamic Template;
  2. Export your historical financial statements from Xero into Excel;
  3. Open the exported financial statements in Excel.
  4. Activate the historical income statement in your dynamic template and load the Import Assumptions tool;
  5. Select Excel/CSV as the source of assumptions;
  6. Select the exported Xero income statement as the source workbook and range;
  7. Select 'New' mapping file in the mapping criteria step to create a mapping file to automate future imports (e.g. when updating the model each month). When importing from Xero you often also need to check the 'Reverse columns' check box because Xero exports are in newest to oldest order across columns;
  8. Map and reconcile the account as you'd normally do when importing directly from Xero; then
  9. Repeat this process for the historical balance sheet.

Going forward, you just need to export the latest month of income statement and balance sheet data and repeat this process, but it will be very quick because the mapping files you've created will automate the mapping in all future importations.

Here's each step of the Import Assumptions process:


Let me know if this workaround works for you. M.
Kevin Ballinger A+ 8

Hi Michael,

OK - yes, you've grasped the concept and I agree this is going to be the best approach with a client who uses Tracking rather than through an elaborate COA in Xero. The frustration as a Xero user is that Xero does not handle this concept well, and although reporting within Xero works for Tracking, there is not (as far as I know) a way to create an income statement template report withTracking categories as ROWS. I'm going to pursue this on Xero forums, but I am aware that this is a weak feature in Xero at the moment. Having said that, products like Futurli integrate via API with this feature and work well - but Futurli is a different animal and IMO is a pure Xero add-on.

I will look into this further and refresh this discussion with updates in due course.


Jun Yan A+ 124

Hi Kevin,

This is a very interesting topic. We work a lot with clients that use Xero tracking or multiple Xero accounts across a consolidated group, and we've had to find solutions to the problem.

We use an add-in called DataDear that allows you to setup a data workbook to pull data at a tracking level (or across multiple Xero accounts) as well as at an aggregated level. We tend to then use the aggregated level + an elimination module to create our consolidated position for modelling purposes in our modular workbook. The benefit of this approach is you can set DataDear to refresh across multiple entities (and an aggregation) in one go and import once, apply eliminations and you have your consolidated historical P&L and BS. It also provides the P&L and Balance Sheet by Tracking Category or Entity across a time series, so you have access at a granular level.

This isn't ideal for all clients, and the approach noted by Michael above works nicely for fewer entities, but when you're working with 10+ entities or 10+ tracking categories, it's nice to be able to push a button and get the data you need...

Hope that helps.


James Longden A+ 103

Good stuff, Jun! 

Is there anything like DataDear for MYOB do you know?

Client with 30 TB's to consolidate...

Cheers. J.

Jun Yan A+ 124

Hey J.

Sorry Jimmy... Limited time in dealing with MYOB. Usually just ask for the exports unfortunately.

Ouch... 30 TB's to export, good luck... I'll keep an eye out though.

Kevin Ballinger A+ 8


That's really helpful. I worked with Datadear a couple of years back for a one-off project and it worked well - I've just not re-visited the app since after hitting some buggy issues durnig that project.

However, that was a long time ago and I will look at firing up a new demo subscription and testing the facility,

Much appreciated!


Jun Yan A+ 124


As far as I can tell, it must be significantly improved since. There are a few things to be mindful of when building out the consolidation, but all in all a very neat package - especially using it in conjunction with a cashflow model.


Sajid Mafahir X 1

Hi All,

With DataDear stopping its support for Xero are there any alternatives for this workaround? 

In addition how would I get to that mapping account box if i would want to remap the accounts? Attached screenshot for reference.


Jun Yan A+ 124

Hi Sajid,

You're really powering through Modano, great!

There's a few options on this.

Modano does actually allow for data to be pulled on a tracking category level, so you can actually build this functionality in a Modano model. I haven't tested it yet, but I'm pretty sure the new Business Units content would be a good place to start.

Our approach was actually to set up a Modano model with a combination of entity + tracking category pulls. We had to do this to ensure that the entity level data reconciled with the tracked data that gets pulled. We then built a Power Query to transform the data into a proper dataset, which we could pivot and move around as we needed to.

We then leveraged Power Query within a Modano model to allow for BU + Entity level modelling to be combined in a single model. This approach just means we need to hit the refresh button once we make updates in the model. We built an error check in there to ensure that people realised that this needed to be done. End result is a set of financials that looks something like this.

Our clients found a lot of value out of having P&L per entity / BU, but balance sheet being done on a consolidated basis was more than sufficient, so it's a bit of a hybrid historical consolidation approach. This also allowed us to run consolidated budget variance + BU level budget variance, all whilst keeping the file size relatively small.

Hope that helps!


Sajid Mafahir X 1

Hey Jun

that really helps but need further clarification on 2 points.

  1. I could not follow through with this point  "We then built a Power Query to transform the data into a proper dataset, which we could pivot and move around as we needed to." is this something integrated by Modano, or does the user has to extract info from Xero then ETL it into Modano model?
  2. I had attached a screenshot in my previous reply to understand how to remap the accounts in Modano import/update assumptions tools.

Lastly and not important, would you have any recommended DatatDear alternatives for Xero? I've been looking at CData, ExlCloud, and G-Accon however, it is not as robust as DataDear. Just looking to upload bank statements to several companies using Excel. it was possible via DataDear before.




Michael Hutchens A+ 189


This thread has drifted a long way away from Kevin's original post, which I'm going to revert back to now.

Kevin - Modano 11 now fully supports importing data based on tracking categories from Xero, and our latest Business Units Model content makes it extremely easy to build tracking category-based multiple business unit models with full reports.

We'll release more information about this over the coming weeks, but you should be able to figure it out pretty easily by upgrading to Modano 11 and then using the New Financial Model wizard (Modano tab, New group) to select a Business Units Model starting model.

It's very cool stuff, let us know what you think.

Michael Hutchens
Managing Director | Modano

Kevin Ballinger A+ 8

Thanks Michael. Have spent a little time with Modano 11 in past few days, and can confirm that this feature absolutely rocks. I have an Event-based Client business where we report, and plan, by Event (Tracking Cat in Xero) - so, I decided to setup a new BU Model from scratch, simple categories, then used Import Assumptions from Xero, for each of their Events/Tracking Cats, and then bundled the Unassigned plus orphan Tracking Cats (using "Add to existing assumptions"). I actually created the import csv by duplicating the original Event mappings file to save alot of drag/drop in the Wizard (client's COA is huge). As with all Xero APIs, the setup is worth spending time on because the monthly refresh is a dream in Modano.

Very nice feature addition - and looking forward to using this more in the future.