# Off Topic > Tips and Tutorials >  >  UK Companies House API

## Kyle123

In case it's useful to anyone, I've put together a somewhat light (and not fully featured) wrapper around the companies house web based API - https://developer.companieshouse.gov...ocs/index.html

It handles the web call, the parsing of the JSON response and maps it to strongly typed objects, allowing you to write code such as this:



```
Please Login or Register  to view this content.
```


There's a workbook attached including the above code in a UserForm, and the necessary classes to encapsulate everything.

To use it, you'll need to sign up for a free developer API key and put that in.

Currently supports:
    - Company Search
    - Officer Search
    - Company Details, including officers

If anyone fancies adding the rest of the data structures (it's very easy, it's just data mapping), I've got a Node (JavaScript) script that generates the classes (ish) from a JavaScript object - most of it is just boiler plate code - let me know and I'll send it over.

Enjoy  :Smilie: 

P.S This won't work on a Mac and I have no inclination to make it do so.

----------


## JapanDave

Another great piece of code gone un-noticed. Well done Kyle.

Dave

----------


## mattwe11s

Hi Kyle

Your file sounds like exactly what I am looking for, however not being an Excel Ninja like your good self I appear to be struggling to get anything out of the doc?

Ultimately I am keen to enter a list of company names and for these to be looked up in Companies House returning address details, reg number, phone number, SIC codes, whether they are still active or not.

Is this what your document does?

Cheers

Matt

----------


## 19marc87

Hi Kyle

I am a novice with Excel coding and i have looked at your spreadsheet and is mind boggling for me, amazing spreadsheet.

I know that your spreadsheet can help me alot but i cant change the code.

What i am looking to do is drop and export of clients in to a excel spreadsheet and using the API connection pull certain field from the beta companies house page for all my clients, so i can check due dates.

Can this be done, i hope so because i am hoping for something like this to help me but i dont know what to do.

Thanks

Marc

----------


## Kyle123

Depends what you need, start a new thread in the forum. Attach a sample workbook with some sample data in and what information you want and how you want it laid out and I'll have a look (if someone doesn't beat me to it  :Wink: )

----------


## 19marc87

thank you, i have created a new thread called 'UK Companies House API Code'

----------


## spinkung

This has saved me days of work, thank you!!!

 :Smilie:

----------


## damnation

This is amazing (I think), but I am not yet there for using it.
I've gone in to where one makes macros and added my API key. But how do I get this code actually to work?
How do I create a userform to make it work? And how does the spreadsheet need to be set up?
Basically I got a list of 2000 companies looking for some details (CRN, Post code, address...)

----------


## Kyle123

Did you download the sample workbook and try it?

----------


## damnation

I have downloaded it, but it's just an empty sheet. I do not see the userform if that's supposed to be there?

Edit: I managed to get the userform! Hooray. One step ahead. Thank you so much!  :Smilie: 
Now... If I had a list with company names and wanted to find the company and then populate the neighbouring cells with the CRN, postcode, address etc, how would I go about that?

----------


## Kyle123

It isn't that easy, how would you deal with multiple results from a company name? But I digress, open a new thread for your question (you can include a link to here to help)

----------


## AliGW

Post removed.

----------


## Kyle123

Ali, bit harsh, this is the tips and tutorials forum so the rules are a bit more lax here  :Smilie:

----------


## Pete_UK

As this is in the Tips and Tutorials forum, I would have thought it was appropriate to allow subscribers to ask questions about it.

Pete

----------


## AliGW

Sorry, chaps - did not realise it was in this section!  :Smilie:

----------


## pjwhitfield

Id just like to say that this is beautiful..........Ive just started looking at a project which involves CH data so this should be ideal starting point.

----------


## Kyle123

No problem, if you use it and add any more to the wrapper, upload it and I'll update my workbook with your additions

----------


## pjwhitfield

hmmm I seem to be having a problem and apologies if it should be in a new thread (Im guessing not but no doubt fast gun Ali will delete if not valid  :Wink:  )

Ive created a key, added it to the constants and attempting searches but each time I get the following error

"The server name or address could not be resolved"

When I debug it steps through and correctly constructs the search query, the baseURl and qry variables hold the expected data but then fails in the .Send (hangs for 5 seconds or so before returning the error).

----------


## Kyle123

What happens if you copy and paste it into the browser? Sounds like you've misspasted your API key

----------


## pjwhitfield

Ive checked and double checked the Key and its spot on.

If I paste the query thats created into a browser
https://api.companieshouse.gov.uk/se...&start_index=0

I just get nothing, it flashes up in bottom left with "Establishing a Secure connection" but nothing else happens.

----------


## Kyle123

Hmmm, I've just tried it and it's working fine. Just to try and rule out some issues, try swapping:



```
Please Login or Register  to view this content.
```


with 



```
Please Login or Register  to view this content.
```


In *CompaniesHouseRequest*

I was being slack with the browser thing, it won't work since you're not authenticated

----------


## pjwhitfield

you sir, are a genius.

Much kudos to you.

----------


## Kyle123

No worries, it's a networky config thing at your work that's causing the issue (probably a proxy server)

----------


## pjwhitfield

hmmm that would make sense it is a seriously secure network

----------


## Picolaud

Hello everyone,

I am new here and am impressed by the work you do here to help on a daily basis.
I just stick on this topic as it seems to be the only one on the internet to tell how to extract companies house's data and to put it in an excel file.
The problem is I am really not an IT expert, and when I open your file Kile, I cannot play the macro.
Reading your comments i might need an API key but I do not know how it works either.

Would you have a sort of a tutorial to make your program work?
Not much details, just some clues, i'll check what to do on other websites.

Thank you very much,

Picolaud

----------


## asifayub786

Hi Kyle,
You sir are amazing, this is awsome work. I got my api key and form worked for me. so thanks to you...
I have a list of companies for which I have to download certain fields and i type them manually everyday, I am wandering if there is any way from company number we could retrieve information like company name, incorporation date or accounts due by date etc on the same line in next columns of worksheet not in a form that would make my life so much easier. don't know even if this is possible, any help is much appreciated.
Many Thanks

----------


## johnny_p

Hi all

I have taken one of Kyle's tools and have managed to tweak it a little to collect a few extra fields.

I am now trying to collect the filing history to collect the transaction_ID for the latest accounts a company.

I can pull in the json file and see the data in the locals windows. From what I can tell the lowest item number is the latest record. I only need this record not the historical data.

Has anyone had any success in pulling just the latest record from a JSON from Companies house?

For some reason I cant upload my file

Appreciate any advise or help.

Thanks

----------


## apostleit

Thanks for this Kyle.  Very impressive.  I would be interested in the script to generate the classes if it's not too much trouble.

Cheers

apostleit

----------


## Kyle123

This was a while ago and I'm not sure I still have it, I'll have  a look tomorrow

----------


## marylin123

Can anyone help me, I'm getting a run-time error 429 when I open the userform.

----------


## Kyle123

You need to post the line of code that you get that on. Are you using a Mac?

----------


## asjmoran

I know that this post is a few years old now but I am hoping someone out there can help me!

This is basically EXCATLY what I have been trying to build and have been met faliure at every step. I addedmy API key but when I try open the userform I get the below;



Please can any help?

Thanks

----------


## Kyle123

That's a bit of an odd error message and I suspect that your version of Excel may need repairing, however we'd need a bit more to go on.

What version of Excel are you running?
Are you using Excel 64 or 32 bit?
Which version of windows are you using?
Is your windows version 64 or 32 bit?
Do you have any missing references?

----------


## J4cob

Hi Kyle123,

I have the same issue as asjmoran.

Running the latest version of Excel office  on a 64bit program and windows 10.

The exact same workbook works on my laptop though (cant remember the version, will check and edit the post). So it doesn't seem to be an issue with workbook. Have also tried reinstalling excel and that did not help.

Any ideas? Could it be my firewall?

Thanks,

----------


## Kyle123

Its likely because youre using 64-bit Excel. If I recall correctly, the Json is being parsed by a library thats not available  on 64 bit Excel

----------


## J4cob

Thanks!. Any way for me to easily amend it to work on 64bit?

----------


## FDibbins

> I know that this post is a few years old now but I am hoping someone out there can help me!
> 
> This is basically EXCATLY what I have been trying to build and have been met faliure at every step. I addedmy API key but when I try open the userform I get the below;
> 
> Attachment 649666
> 
> Please can any help?
> 
> Thanks



*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------


## Pete_UK

Hi Ford,

we've had this discussion in other threads. As this thread is in the Tips & Tutorials forum, it is perfectly reasonable to allow other contributors to ask questions about it - see Post #14.

Pete

----------


## joeburg

Hello!

Is there any way to search for companies via name and it pulls the first result from the search into a cell?

So i'd search for example "tesco" and it would pull back "Tesco PLC" and "00445790"

Appreciate the work you've done  :Smilie:

----------

