UK Companies House API

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

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

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

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

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 )

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

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

Did you download the sample workbook and try it?

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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?

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,

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

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

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