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>

 

[boxhamster]

Hi,

There is a thread on this forum where Kyle has posted an actual spreadsheet that you can use and modify. You'll need your API token code to get it working. You get that from companies house after creating an accoutn.

 

[Leyla]

Hi All, i am looking for exact same thing, i have company registration numbers in excel and looking for a way to get more info from companies house like registration date, address, postcode , confirmation statement filed till, accounts submitted till, first director name etc in each column. OP did you manage to get what you were looking for. I have created my Api token code aswell. I have looked at the Kyle's file suggested here, it sort of pulls the info but my knowledge is too limited to modify it. any help in this regard is highly appreciated. Many thanks