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

[boxhamster]

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

/*
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>

 

[Kyle123]

Does it have to be PowerQuery? Twitter uses a different authorization method so your code is a long way off.

 

[boxhamster]

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?

 

[Kyle123]

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?

 

[boxhamster]

Aha. Interesting. I shall try to figure this out with VBA then. Thanks for the suggestion.

 

[Kyle123]

I can help you with that if you answer my questions above

 

[boxhamster]

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

 

[Kyle123]

Again, specifically, what information are you after? I've extracted info from this API before, but there's a lot in there

 

[boxhamster]

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.

 

[asifayub786]

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