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



## boxhamster (Jan 13, 2016)

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 (Jan 13, 2016)

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


----------



## boxhamster (Jan 13, 2016)

Kyle123 said:


> 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 (Jan 13, 2016)

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 (Jan 15, 2016)

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


----------



## Kyle123 (Jan 15, 2016)

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


----------



## boxhamster (Jan 15, 2016)

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 (Jan 15, 2016)

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


----------



## boxhamster (Jan 15, 2016)

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 (Dec 8, 2017)

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


----------



## boxhamster (Jan 13, 2016)

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 (Dec 12, 2017)

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 (Jun 6, 2018)

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


----------

