Power Query and the Companies House JSON API (UK) - How to get started?

boxhamster

New Member
Joined
Jan 12, 2016
Messages
14
Hey,

In the UK all the companies are registered in "Companies House" and an API exists for this now to access info like address, revenue etc. (https://developer.companieshouse.gov.uk/api/docs/index.html)
What I have is a list of company names and I know that I first need to find the CRN number they use for each company then using that number I can pull in all the information I need. I also know they need authorisation and I have registered with them.

I have now come to the end of my knowledge and don't know how to do this in Excel+Query. I'm on Office 2016.

Would anybody be able to help me navigate through all of this? I watched some videos on YouTube and read some guides and I think I need something like this below.

I would appreciate your guidance.
Thanks

Code:
/*
This M script gets an bearer token and performs a tweet search from the Twitter REST API
https://dev.twitter.com/oauth/application-only
 
Requires establishing a Twitter application in order to obtain a Consumer Key & Consumer Secret
https://apps.twitter.com/
 
IMPORTANT - The Consumer Key and Consumer secret should be treated as passwords and not distributed
*/
 
let
 // Concatenates the Consumer Key & Consumer Secret and converts to base64
 authKey = "Basic " & Binary.ToText(Text.ToBinary("<consumerkey>:<consumersecret>"),0),
 url = "https://api.twitter.com/oauth2/token",
 // Uses the Twitter POST oauth2/token method to obtain a bearer token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("grant_type=client_credentials") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),
 // Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken,
 // Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method
 GetJsonQuery = Web.Contents("https://api.companieshouse.gov.uk/company/{company_number}",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
 NavigateToStatuses = FormatAsJsonQuery[statuses],
 TableFromList = Table.FromList(NavigateToStatuses, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 ExpandColumn = Table.ExpandRecordColumn(TableFromList, "Column1", {"metadata", "created_at", "id", "id_str", "text", "source", "truncated", "in_reply_to_status_id", "in_reply_to_status_id_str", "in_reply_to_user_id", "in_reply_to_user_id_str", "in_reply_to_screen_name", "user", "geo", "coordinates", "place", "contributors", "is_quote_status", "retweet_count", "favorite_count", "entities", "favorited", "retweeted", "lang", "possibly_sensitive", "quoted_status_id", "quoted_status_id_str", "quoted_status"}, {"Column1.metadata", "Column1.created_at", "Column1.id", "Column1.id_str", "Column1.text", "Column1.source", "Column1.truncated", "Column1.in_reply_to_status_id", "Column1.in_reply_to_status_id_str", "Column1.in_reply_to_user_id", "Column1.in_reply_to_user_id_str", "Column1.in_reply_to_screen_name", "Column1.user", "Column1.geo", "Column1.coordinates", "Column1.place", "Column1.contributors", "Column1.is_quote_status", "Column1.retweet_count", "Column1.favorite_count", "Column1.entities", "Column1.favorited", "Column1.retweeted", "Column1.lang", "Column1.possibly_sensitive", "Column1.quoted_status_id", "Column1.quoted_status_id_str", "Column1.quoted_status"})
in
 ExpandColumn
</consumersecret></consumerkey>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Does it have to be PowerQuery? Twitter uses a different authorization method so your code is a long way off.
 
Upvote 0
Does it have to be PowerQuery? Twitter uses a different authorization method so your code is a long way off.

Thanks for your answer.

I have all these names in Excel and would like to add the other information via the API. I just assumed it has to be PowerQuery. What other methods are there?
 
Upvote 0
You can just use standard vba. Specifically what info are you after, I don't think revenue for example is published on the API. Are you wanting a process flow, so firstly search for the company, then select the correct on from the results (using a userform or something), then go fetch the relevant data?
 
Last edited:
Upvote 0
Hey,

That is so nice of you to ask.
Yes, I have collected a list of business names that I would like to gather some information for. So first would be to find the correct business name (mrexcel -> MREXCEL Ltd) and the use that to get the address in neighbouring columns. I have been reading around this for a while now, but would appreciate some pointers.

Thanks
 
Upvote 0
Again, specifically, what information are you after? I've extracted info from this API before, but there's a lot in there
 
Upvote 0
Great. That's amazing to here.
I am looking for: Company name, CRN, postcode, address, SIC, nature of business (SIC), status of business (if active or not etc). Thank you so much.
 
Upvote 0
Hi
I am in exact same situation as OP where i have company names and registration numbers and need to extract address, accounts submitted till, accounting reference date, confirmation statement file tiil. Op did you get what you wanted? I have been reading about it and have so far got my api from companies house. I have office 2016 and 2013 with power query. Any help in this regard is highly appreciated. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top