Excel Data Links

[matthew2]

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?

[voracityemail]

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:

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.

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

Companies House Excel custom functions released

See here for an animated gif of what this is. https://s1.gifyu.com/images/excel-custom-functions-video.gif Microsoft released their preview mode for Excel Custom Functions earlier in the month. I've created a project that maps company lookup end-points to excel functions. This allows an excel user to retrieve company directors, accounting year-end, current status etc using custom functions. See project github here Functions include: - =COMPANY.ACCOUNTINGDATE - Company's accounting date - =…

• 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:

• the Companies House docs (noting that there are a few inaccuracies / missing bits)
• Have a brief understanding of using a REST API - particularly the basics of the http connections with that.
• Check out their documentation on authorization / authentication and make sure you know what’s happening there (in the http basic authorisation) as that seems to trip up lots of people.
• Have a means of understanding the data you get back (e.g. the JSON format and the Companies House data structures).

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?

401 Code - API Problem in Excel VBA

Hi Experts, I am wandering if someone can help with the following: I am using excel VBA to execute the search API and not getting any where. I am receiving request status 401 in other word not authorised to use the API. Code: Private Sub CommandButton1_Click() Dim xmlhttp As Object Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0") 'Dim xmlhttp As New MSXML2.xmlhttp 'Dim xmlhttp As New MSXML2.XMLHTTP60 for Microsoft XML, v 6.0 Dim myurl As String myurl = "https://api.companieshouse…

[frank]

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