How to get data into a spreadsheet?

Hey,

I am very interested in learning about how to use this API, but I am very new.
If I had a list of companies in a spreadsheet and wanted to search for the name in order to populate a column with the company number. How would I do this?

Thanks for your help!

Best regards.

Patrick,

Thanks for the question.

In terms of the API you will need to do the following: -

  1. Register for an API key on the developer hub.
    https://developer.companieshouse.gov.uk/api/docs/index/gettingStarted/apikey_authorisation.html
  2. Using the company search end point search for the company using its name or number, documentation as follows:
    https://developer.companieshouse.gov.uk/api/docs/search/companies/companysearch.html

In terms of plugging this code into excel then I would suggest using VBA, but I’m no expert in Excel, I’m sure a Google search will assist!

Please feel free to post any further questions.

Thanks

Mark.

Hi Patrick,

did you have any luck with this, i am newbie in this and I am very interested in gettings companies house data in to excel spreadsheet columns from company numbers that i already have. any help in this regard is highly appreciated.

Many Thanks

Depending on what your requirements were, the simplest way to do this might be to use the CSV files (Excel can import these) from the Companies House data product. This will allow you to get working without any additional coding / registration.

Limitations:

  • The information is updated monthly
  • The dataset is large, so each file only covers a part of it.
  • Each file is a large amount of data (you may have issues with speed / memory usage in Excel)
  • I’m not sure why (memory usage?) but CH recommend Excel 2007 or greater. Works fine with LibreOffice for me however.
  • All active companies should be listed but there may be limits on ceased / very old companies? The API dataset itself has limitations in this regard (e.g. companies removed 6 years after ceasing). The (interactive) webCHeck site seems to be the most complete in this regard.

If you need more recent / live data either:

  1. Go for the API. Check the documentation to see how to work it / if it covers what you need. Search this forum for information on getting it to work with your chosen environment / language (e.g. Excel + VBA / VBScript / Visual Basic, .NET or whatever). Sign up as per instructions above.
  2. If this is overkill it may be slightly simpler to get information using the companies URIs. No sign-up needed, you just request a web resource (URI) and it will return basic company data in your chosen format (including CSV). There’s an overview on the gov.uk site.

Hi Voracityemail,

Many thanks for your time and guidance. Sorry I couldn’t explain it earlier didn’t think someone will reply this quickly. I basically need live company profile data as I have a tracker and 100s of companies to update their data manually. I have attached a sample of tracker below if there is any way to have this automated that will save my life. i am no expert I have created an account with companies house and have granted API to excel from here https://developer.companieshouse.gov.uk/developer/applications. What I don’t know is how to use URI in Excel or GET command in excel. any help is much appreciated.

Tracker File attached here

Regards,

Asif

(Quick response) - you got lucky (I’m not part of Companies House). Caveat - I haven’t used http in Excel or tested any of the below.

Questions about how to send http requests to which return data to Excel might be better addressed on an Excel forum. There’s no harm in you asking in case someone else has done it here but your request does sound a bit like “do my homework for me”. Did you search the forum (“magnifying glass” icon, top right)?

Searching here just now I found a thread in which someone’s (incidentally) documented what they were doing to connect to Companies House from Excel - maybe you could start there?

Someone’s already written code to automate some things in Excel and made it available:

Two quick points:

  • If you’ve got a lot of data, don’t forget about the rate-limiting in the API - if you exceed this CH may block your access.
  • There may be a simpler solution by exporting your existing data from Excel (e.g. to CSV) and getting another program to use this to do the Companies House lookup and slot the data you need back in to your file.

A quick Google just found help with http / Excel:

Good luck.