Excel Data Links

API Question
1

I am an accountant with some programming experience in Python and C but I am having difficulty trying to understand the API functionality.

I have a spreadsheet that tracks all our clients and whether accounts and tax have been submitted etc but it is very manual.
I’d like to get the spreadsheet to feed data in from Companies House by looking up the company number in column a and returning the accounts due date for a start.

Does anyone have any guidance or resources to direct me to or if this is even possible?

2

Do you need the API at all? Companies House does publish (fairly) regularly updated static data sets. From your description is sounds like the main one - company data dataset - might cover you:

  • Main company details (name, number, office etc), company status.
  • Date of last accounts or confirmation statement filed.
  • Date of next accounts or confirmation statement due.

Possible snags:

  • No mention of tax - but the API doesn’t get you this (directly) either.
  • This is updated monthly.
  • The files are in CSV format but they are very large - you likely need processing outside of Excel to trim them.

Available from: Companies House

If you need more accounting detail any accounts filed electronically can be found at:
http://download.companieshouse.gov.uk/en_accountsdata.html

  • Pro: very detailed (e.g. you get what is actually filed).
  • Con: very detailed - you need to be able to process the file format.
  • Updated every five days.
  • Not all companies file electronically?

Assuming that won’t cover you, you’ve two choices:

  1. The main API - just request data you want for each company you need.
  2. The “Streaming API” - appropriate if you have a large number of companies and want to track updates to them when they happen. I’ll not cover this, I suspect that’s not what you need but if so just search the forum.

There’s a similar question with various answers at this thread:

https://forum.aws.chdev.org/t/how-to-get-data-into-a-spreadsheet/324

Is your spreadsheet package Excel? Someone’s already written code to automate some things in Excel and made it available:

  • Looks neat e.g. has a custom add-in which adds Excel functions which communicate with Companies House.
  • Requires some extra setup e.g. a local web server, putting in some add-ins to Excel. It’s also 2-3 years ago now since last updates.
  • I haven’t used it myself.

If you’re not covered yet, you can go ahead and build your own.
Again I don’t know your spreadsheet but there are plenty of C / Python examples on the forum. If you were planning to build this in to Excel e.g. using VBA there’s an example I’ve listed below. The API usage is pretty simple albeit with some common pitfalls and some “features”. You should find enough help here on the forum to get your through. Just make sure you’ve read / understood:

Finally you’ll need to register (free) with Companies House. You need to register an IP address / domain so you will likely need server space or you can use a locally hosted web server. The latter requires a workaround at present for localhost.

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?

3

Matthew

We have an automated subscription module that does this in real time (currently for a different purpose and variable field, but principal is identical) - should you want to explore then e-mail

admin@statbooks.co.uk