Annual accounts for Microsoft Denmark using #PowerBI and XBRL

The Danish Central Business Register -https://datacvr.virk.dk/data/?language=en-gb& contains a lot of data including the possibility to find the annual account for all companies in Denmark.

These are available in two formats – pdf and XBRL format – so for instance for Microsoft Denmark ApS we can find the latest Annual Accounts (financial statement) via this search

https://datacvr.virk.dk/data/visenhed?enhedstype=virksomhed&id=13612870&soeg=microsoft&type=Alle&language=en-gb

As you can see, we can get the document via the link XBRL and get the annual account in XML format like this

You can read more about the XBRL format here – https://www.xbrl.org/

So knowing that we can search for companies using their CVR number and find the latest account let’s try to retrieve the data using Power BI Desktop.

Find the company data

Open the Power BI Desktop and choose get data – and the source is Web

This will give you the HTML document

But we want to find the links to the XBRL accounts so we actually don’t want it opened as an HTML document – so click Edit and in the steps pane click the source to change it to open the document as a Text file

And delete the navigation step after Source.

Now we can find all the rows where XBRL is

This gives us the three rows with information about the link to the accounts in XBRL format

Then trim the column and Split the value by ” – gives us a column 1.8 containing the link to the file.

Now we can delete unwanted columns, add an index and filter by 0 in the index column to get the latest account

Now we have a table with a link to the latest XBRL account for a given company.

Retrieve the account information

To get specific data in the accounts we can use this XBRL link to do a new query retrieving the data

So click on Home tab and New source and select Web again

https://datacvr.virk.dk/data/offentliggorelse?dl_ref=ZG9rdW1lbnRsYWdlcjovLzAzLzQwLzg1LzJkL2ZhL2VjZTItNDViMC1iOTFjLWQzOGUzZTIzZTRhYQ

This gives us the navigator to the XML document

If you are interested in specific elements you can tick those – but click for instance Context and click OK and via the Query Settings click source

Click on table in the node – http://xbrl.dcca.dk/fsa

And you get all the account lines

Then expand the Table column and choose all elements

Now in order to get the current years data you can filter by the contextRef c1

Now after some filtering and removing of duplicate rows – we end up with the lines for the profit and loss as seen here

The values are all positive so in order to handle this – we can pivot the data and do multiplication with -1 and unpivot the columns again

Then we can load this data and visualize the Profit and loss in a waterfall chart

By default the waterfall sorts the data by A-Z which isn’t exactly what we want – luckily there is a workaround for this

Choose Edit Queries and add an Index column to the Context query.

Load the data and select the Attribute in the field pane – and in the Modeling select Sort by Column and choose to sort by Index

And after some formatting etc. we can illustrate the Profit & Loss for Microsoft Denmark like this

Now we can wait a few month to the next Account is done and just click refresh.

Using functions and/or parameters you can make this highly flexible – if this post gets more than 10 likes J – I promise to show you how that can be done.

Hope you liked this – if you want to play around with you can download the pbix here.