UK Companies House API Code

Hello

I was reviewing the forum for a solution to my thought and came accross the thread 'UK Companies House API' a chap named Kyle123 developed a spreadsheet which linked up to companies house via a spreadsheet and pulled particular information.

I am learning on excel and trying to learn code.

I am unfortunately too much of a novice to understand the code on the spreadsheet.

What i am trying to achieve is a link to Companies house via an API which would pull the following data from Companies house based up a the 'company registration number'
I would like to see the following results 'Next Accounts', 'Next Accounts Due By', 'Confirmation Due by', Registered Office'

Here is an example of the spreadsheet i would like to show:

Company Name Company Number Next Accounts Next Accounts Due By Confirmation Statement Due By Registered Office Addres
A Limited 1234567 (Date) (Date) (Date)
B Limited 1234567 (Date) (Date) (Date)
C Limited 1234567 (Date) (Date) (Date)
D Limited 1234567 (Date) (Date) (Date)


Thank you

Marc

Please upload a workbook, not copy and paste into the thread, it makes it easier to help you since no-one has to re-create your workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please also include some actual company registration numbers so someone doesn't have to go searching for some to help.

Finally, my mentioned thread is here if anyone is looking to help: http://www.excelforum.com/tips-and-t...house-api.html

Hi Kyle

Thank you for your reply

I have attached a spreadsheet, i hope its not too rubbish.

I have 3 sheets, first 1 is what it would look like before the code initialized, i would like to drop the the information into the spreadsheet on the second sheet.

Then 3rd sheet is the results, but its would be for more multiple such as 300 or more.

The purpose is to be able to look up data to Companies House website on a mass basis and identify deadlines and any discrepancies.

Thanks

Marc

@op I'll hAve a look at this in the morning for you

@ab33 can't you just stick the Windows disk in and reboot? I tend to do that about every 6 months or so. My code doesn't do that now but it would be pretty easy to add that if needed, I only mapped the data I needed at the time

Hi Kyle,
I had the same issue with windows 7. My other windows XP PC still shows the same error. In fact, I had re-installed windows 7 before I upgraded to windows 10. Trust me, it might be easier to fix this error for some one like you who is computer savvy. I have no idea what is mapping.
This is very common error and the help from MS is the same crap-install and re-install MS office. After lots of search, I have pinned down the error to missing some thing from the registrar.
This website has good info.
http://mkksharepoint.blogspot.co.uk/...component.html
but can take the chance to destroy my PC.

@AB33 I'd do it and wipe my machine if it's a problem. Re-installing windows is pretty trivial, especially with win 10, I can do a clean build and have all my applications installed and updated in under a day (most of it is just clicking next).
@Marc, you want something like the attached. I've also included the pertinent code below:

Please Login or Register  to view this content.

You will obviously need to get your own API key (and insert it into the CONSTANTS module). In addition, the rate is throttled, so expect this to stop working if you run > 600 queries every five minutes.

I wipe every six months does wonders for performance

Hi Kyle,
Do you use some of kind of software to re-install your applications, or is it something built-in windows 10?

Hi Kyle

Thank you.

I have inputted my API key.

What is my next step how do i run the code once i have inputted my population.

Thanks

Marc

Hi Kyle

Thank you.

I have inputted my API key.

What is my next step how do i run the code once i have inputted my population.

Thanks

Marc

Hi IonutC,
Well, unless there is some sort of auto mechanism to retrieve the back-up data and all applications, why would any one will reboot their PC every 6 months? Imagine the amount of time takes to install your previous applications.

@AB33 & IonutC, I've answered here so we don't keep hijacking

http://www.excelforum.com/the-water-...ix-months.html

@Marc

I've attached with a button on the worksheet to make it easier

Thank you

When i chick the button, i am getting a error message 'run-time error 110021: Syntax Error

Thank you Kyle, i am such a divy haha

it works great, however, i have listed 280 clients and i get the following error after line 79 "Object variable or with block variable not set"

Also, majority of the Year End Accounts Date is only bringing through 01/01/1990 instead of the date

Thanks

Marc

Could you post a workbook with the offending company numbers in?

Sorry but i wont be able to because of data protection.

Everything pulls through apart from the majority of the Year End Dates

Why data protection? It's public information. I can't help you if you can't give me an example

here is an example of the clients
2015 Solutions Ltd 09955615
4gk Consultants Ltd 09928394
AG1 Consultancy Limited 10055196

ok i understand now, is there a way to but in the code if it is the first year then put 'first accounts made up to'

you have been great by the way

Is there a way to pull the actual date of the first accounts date, i have inputted the code and its the wording that comes up

Which is the first accounts date in the below:

Please Login or Register  to view this content.

},
"accounts": {
"next_made_up_to": "2016-04-05",

do you want that instead of the last accounts made up to?

Yes please, i can add in the code

i mean if you can post it on here plz

Please Login or Register  to view this content.

this is fantastic, thank you.

i was wondering whether you could us an API code to push information to an accountancy book keeping cloud based software?

Yes, if the accountancy cloud software has a published api that allows it.

Hi Kyle

Yes it does allow it, it has a separate API per client.

Can you help me in adding an additional column on the spreadsheet for pulling the company registered number

Thanks

marc

This is my poor guess until Kyle pops in

cll.Offset(0, 6).Value = company.Company_number

Hi, sorry, been on holiday.

Can you help me in adding an additional column on the spreadsheet for pulling the company registered number

All the code above assumes that you already have the registration number. If you don't the process is much more involved. It's quite likely that searching by business name will return multiple results, how would you want to see this? How would you determine the correct one? You're starting to get into userform territory. In fact, my original example in the other thread shows you how to search by company name and return the associated information.

Hi guys. I have stumbled across this read, which could be pretty useful for my line of work.

With regards to an API for this module to work, where would one be obtained from?

Many thanks.

Hi guys. I have stumbled across this read, which could be pretty useful for my line of work.

With regards to an API for this module to work, where would one be obtained from?

Many thanks.

This is not working for me. I have got an API key from CH and put that in the CONSTANTS module.

I am then running the macro via the button which Kyle added in.

I am then inputting a company name and number and pressing get data and get this error:

Run-time error '-2147012867 (80072efd)':

A connection with the server could not be established

When I choose 'Debug' I get this code highlighted:

Please Login or Register  to view this content.

Any help please?

Hi guys/girls

I found this post which seems really helpful and close to what I'm after.

Does anyone know how to add additional fields from Companies House through the API? I have spent a bit of time trying to add a few extra fields with no luck.

I'm after the Transaction_ID from the filing history. I'm after the latest full annual accounts which have been published.

thanks in advance

Hello,

i am struggling with it any just want to know how do you input the api key.
Please help.

Hello,

I am struggling with it any just want to know how do you input the API key.
If you have any new version of file running smoothly.
Please help.
Regards.