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:

Option Explicit

Dim api As CompaniesHouseAPI

Private Sub UserForm_Initialize()
    
    Set api = New CompaniesHouseAPI
    
    api.companiesHouseBaseUrl = Constants.BASE_URL_API
    api.companiesHouseApiKey = Constants.API_KEY
    
    Me.getOfficers.Enabled = False
    
End Sub

Private Sub searchButton_Click()
    
    Dim companySearchResults As CHSearchResultCompany
    Dim company As CHSearchResultItemCompany
    Dim x As Long
    
    Me.ListBox1.Clear
    
    Set companySearchResults = api.companySearch(Me.searchBox.text, 50, 0)
    
    MsgBox "Total results found:" & companySearchResults.total_results
    
    Me.ListBox1.ColumnCount = 4
    Me.ListBox1.ColumnWidths = "0;250;250"
    
    For Each company In companySearchResults
        Me.ListBox1.AddItem
        Me.ListBox1.List(x, 0) = company.company_number
        Me.ListBox1.List(x, 1) = company.title
        Me.ListBox1.List(x, 2) = company.description
        x = x + 1
    Next
    
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    Dim company As CHCompany
    
    If Me.ListBox1.ListCount > 0 Then
        
        Set company = api.companyById(Me.ListBox1.Value)
        
        Me.CompanyName = company.company_name
        Me.CompanyNumber = company.company_number
        Me.CompanyStatus = company.company_status & " - " & company.company_status_detail
        
        With company.registered_office_address
        
            Me.CompanyAddress = .address_line_1 & vbCr & _
                                .address_line_2 & vbCr & _
                                .region & vbCr & _
                                .country & vbCr & _
                                .postal_code
                    
            
        End With


        Me.getOfficers.Enabled = True
        
    End If
End Sub

Private Sub getOfficers_Click()
    
    Dim officers    As CHOfficerList
    Dim officer     As CHOfficerListItem
    Dim temp        As String
    
    If Len(Me.CompanyNumber.Caption) > 0 Then
        
        Set officers = api.companyById(Me.CompanyNumber.Caption).officers
        MsgBox "Officers listed: " & officers.active_count
        
        For Each officer In officers
        
            temp = temp & officer.name & vbCr
            temp = temp & officer.nationality & vbCr
            temp = temp & officer.occupation & vbCr
            temp = temp & officer.country_of_residence & vbCr
            temp = temp & vbCr
            temp = temp & "----------------------------------"
            temp = temp & vbCr
            
        Next officer
    
        MsgBox temp
    
    End If
    
End Sub

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.

[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 )

[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!!!

[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!
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.

[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

[Kyle123]

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

[AliGW]

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

[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

[marylin123]

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

[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 )

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:

winhttp.winhttprequest.5.1

with

MSXML2.XMLHTTP

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

[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

[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

[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.

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

[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?

[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