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
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
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.