API UK Establishment companies

API Question
1

HI ,

I have a VBA macro in excel to extract some fields for the UK Establishment companies.

The company Profile Path is working fine
GET https://api.company-information.service.gov.uk/company/{companyNumber}

But when I change to the UK Establishment doesn’t get any data
GET https://api.company-information.service.gov.uk/company/{company_number}/uk-establishments

Is anyone experience the same or could point me on the right direction if I am not using it correctly, please?

Full VBA Code Below
Any help is much appreciated

Thanks,
Carlos

2

Public Sub GetCompaniesHouseData()

'Get API credentials first
Call SetAuth

Dim inputWs As Worksheet
Dim LastInputRowWithData As Integer
Dim i As Integer
Dim RegNo As String
Dim OrigCompName As String

Set inputWs = Sheets("Input")
LastInputRowWithData = inputWs.Range("A1").End(xlDown).Row

Sheets("Main").Cells(6, 3).Value = 0
Sheets("Main").Cells(6, 5).Value = LastInputRowWithData

Dim OutputWs As Worksheet
Dim http As Object
Dim JSON As Object
Dim Path As String

Set OutputWs = Sheets("Output")
Set http = CreateObject("MSXML2.XMLHTTP")
  
For i = 2 To LastInputRowWithData
     
     OrigCompName = inputWs.Cells(i, 2).Value
     RegNo = inputWs.Cells(i, 1).Value
     If IsNumeric(RegNo) = True Then
        RegNo = Format(RegNo, "00000000")
     End If
     
     'MsgBox (LastInputRowWithData)
     
     Path = "https://api.company-information.service.gov.uk/company/" & RegNo & "/uk-establishments"
    'Path = "https://api.company-information.service.gov.uk/company/" & RegNo  (THIS ONE IS WORKING FINE!)
    
 
     'MsgBox (Path)
     
     http.Open "GET", Path, False
     
     http.setRequestHeader "Authorization", _
         "Basic " + Base64Encode(BasicAuthUser + ":" + BasicAuthPwd)
     
     http.Send
     
     CheckHTML = InStr(http.responseText, "<html>")
     NotFound = InStr(http.responseText, "company-profile-not-found")
     
     If CheckHTML > 0 Or NotFound > 0 Then
        OutputWs.Cells(i, 1).Value = RegNo
        OutputWs.Cells(i, 2).Value = OrigCompName
        OutputWs.Cells(i, 3).Value = "Not found"
        OutputWs.Cells(i, 4).Value = ""
        OutputWs.Cells(i, 5).Value = ""
        OutputWs.Cells(i, 6).Value = ""
        OutputWs.Cells(i, 7).Value = ""
      Else:
        Set JSON = ParseJson("[" & http.responseText & "]")
        On Error Resume Next
        For Each Item In JSON
            OutputWs.Cells(i, 1).Value = Item("company_number")
            OutputWs.Cells(i, 2).Value = OrigCompName
            OutputWs.Cells(i, 3).Value = "Found"
            OutputWs.Cells(i, 4).Value = Item("company_name")
            OutputWs.Cells(i, 5).Value = Item("company_status")
            OutputWs.Cells(i, 6).Value = Item("locality")
            OutputWs.Cells(i, 7).Value = Item("type")
   
        Next
      End If
     
     'Pause 1 second so as not to exceed API rate limit
     'Application.Wait (Now + TimeValue("00:00:00"))
     Application.Wait (Now + TimeSerial(0, 0, 1) / 10)
     
     
     'Update Progress
     Sheets("Main").Cells(6, 3).Value = i
     
Next

End Sub

3

Which companies are you looking up?

Companies with the “uk-establishments” endpoint are “foreign companies” - they have their own special company numbers e.g. they start with “FC”. In fact Companies House will have one “FC” and then a number of entries for the actual “UK establishment” starting with “BR”. The documentation shows what to expect back. Here’s an example I just requested via curl (I’ve cut some data for clarity):

curl -u OUR_API_KEY: "https://api.company-information.service.gov.uk/company/FC022268"
{
"registered_office_address" : { ... "country" : "United States" },
"company_number" : "FC022268",
"type" : "oversea-company",
"company_name" : "PRINCETON UNIVERSITY PRESS",
"links" : {
     "uk_establishments" : "/company/FC022268/uk-establishments",
     ...
},
"foreign_company_details" : { ... },
"external_registration_number":"EIN 21-0634483",
....
}

Now look at the links.uk_establishments:

curl -u OUR_API_KEY: "https://api.company-information.service.gov.uk/company/FC022268/uk-establishments"
{
    "items": [
        {
            "company_number" : "BR011501",
            "company_name":"PRINCETON UNIVERSITY PRESS",
            "company_status":"open",
            "links": { "company" : "/company/BR011501" }
        }
    ],
    "kind":"related-companies",
    ...
}

As you can see it’s possible to have one or more (I think actually zero or more) “UK establishments”.
You can of course request the company profile of any of those:

curl -u OUR_API_KEY: "https://api.company-information.service.gov.uk/company/BR011501

So it may simply be that you don’t have any company numbers of the form FCnnnnnn that you’re calling this on!

If you are stuck try searching for some well-know international companies (banks, airlines and car firms are good ones) on https://find-and-update.company-information.service.gov.uk/

A couple of examples:
FC004087 - BR001160
FC003821 - BR011768
FC003748 - BR003028

Aside - I’m not sure why you’ve needed to wrap the response text in array brackets:

Set JSON = ParseJson("[" & http.responseText & "]")

… the server responds with JSON so unless VB is doing something odd or the ParseJson call is you shouldn’t need that. However I haven’t used these functions in VB myself.

1 Like
4

Hello @voracityemail , many thanks for your reply :slight_smile:
Your links.uk_establishments: It is exactly what i am looking for …

I am Looking up FC020086 - BR003764. as an Example

For the First example my code is working
“https://api.company-information.service.gov.uk/company/FC022268”…

But for the Second
“https://api.company-information.service.gov.uk/company/FC022268/uk-establishments”…

It does not , so not sure what i am missing …?

The original VB code (Excel Macro) was created from a Ex work colleague, so probably you are right about the ParseJSON…
I have 2 tabs on the excel one to load the companies names i would like to lookup and other to show the results.
So I am just try to adapt it to extract the Establishments companies on the same way , and then I will load the results on a SQL table with SSIS .

5

I can’t debug your code for you but one thing jumps out:

For Each Item In JSON

Again I don’t know what your parser is doing but you will get different JSON structures back from different endpoints in the API. Check the API documentation to see what you’re getting back. It’s your job as a coder to tell your code how to deal with it! The uk-establishments endpoint will return a “resource” (a JSON-encoded object) which contains a member called items, which is an array and so presumably your can iterate over that with For Each. Your code doesn’t appear to be doing that… (If you’re not familiar with JSON I’d quickly read up on that too - very simple but how different systems handle it will have some differences).

If I was in your position I’d first feed the link to the uk-establishment in on the command line and ensure this works and I got back what I thought I was getting. You can download the curl tool for most operating systems, including Windows if you don’t have that or something like it already.

After that I’d check in Excel (by dumping the string(s) into a cell - or using a debugger) that the string I was sending was correct and what text I got back in the http response - if I didn’t get anything then I’d check for errors. I’d then check what the output from ParseJson actually was etc.

Finally - not that this is the issue but it’s possible that the variable names aren’t helping you e.g.

… is not actually the name but the company number (which must be 8 characters but can start with one or two non-numeric characters). Always worth trimming your string to ensure you don’t have leading / trailing spaces…

6

Many thanks, very helpful information

I am going to try Curl tool as I was just reading about it too …

A lot to be learn about API… :slight_smile: