+ Reply to Thread
Results 1 to 39 of 39

UK Companies House API

  1. #1
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    UK Companies House API

    In case it's useful to anyone, I've put together a somewhat light (and not fully featured) wrapper around the companies house web based API - https://developer.companieshouse.gov...ocs/index.html

    It handles the web call, the parsing of the JSON response and maps it to strongly typed objects, allowing you to write code such as this:
    Please Login or Register  to view this content.
    There's a workbook attached including the above code in a UserForm, and the necessary classes to encapsulate everything.

    To use it, you'll need to sign up for a free developer API key and put that in.

    Currently supports:
    - Company Search
    - Officer Search
    - Company Details, including officers

    If anyone fancies adding the rest of the data structures (it's very easy, it's just data mapping), I've got a Node (JavaScript) script that generates the classes (ish) from a JavaScript object - most of it is just boiler plate code - let me know and I'll send it over.

    Enjoy

    P.S This won't work on a Mac and I have no inclination to make it do so.
    Attached Files Attached Files

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: UK Companies House API

    Another great piece of code gone un-noticed. Well done Kyle.

    Dave
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Registered User
    Join Date
    04-07-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    1

    Re: UK Companies House API

    Hi Kyle

    Your file sounds like exactly what I am looking for, however not being an Excel Ninja like your good self I appear to be struggling to get anything out of the doc?

    Ultimately I am keen to enter a list of company names and for these to be looked up in Companies House returning address details, reg number, phone number, SIC codes, whether they are still active or not.

    Is this what your document does?

    Cheers

    Matt

  4. #4
    Registered User
    Join Date
    09-22-2016
    Location
    England
    MS-Off Ver
    2010
    Posts
    28

    Re: UK Companies House API

    Hi Kyle

    I am a novice with Excel coding and i have looked at your spreadsheet and is mind boggling for me, amazing spreadsheet.

    I know that your spreadsheet can help me alot but i cant change the code.

    What i am looking to do is drop and export of clients in to a excel spreadsheet and using the API connection pull certain field from the beta companies house page for all my clients, so i can check due dates.

    Can this be done, i hope so because i am hoping for something like this to help me but i dont know what to do.

    Thanks

    Marc

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    Depends what you need, start a new thread in the forum. Attach a sample workbook with some sample data in and what information you want and how you want it laid out and I'll have a look (if someone doesn't beat me to it )

  6. #6
    Registered User
    Join Date
    09-22-2016
    Location
    England
    MS-Off Ver
    2010
    Posts
    28

    Re: UK Companies House API

    thank you, i have created a new thread called 'UK Companies House API Code'

  7. #7
    Forum Contributor spinkung's Avatar
    Join Date
    10-27-2006
    Posts
    199

    Re: UK Companies House API

    This has saved me days of work, thank you!!!


  8. #8
    Registered User
    Join Date
    12-09-2016
    Location
    berlin
    MS-Off Ver
    Office365
    Posts
    4

    Re: UK Companies House API

    This is amazing (I think), but I am not yet there for using it.
    I've gone in to where one makes macros and added my API key. But how do I get this code actually to work?
    How do I create a userform to make it work? And how does the spreadsheet need to be set up?
    Basically I got a list of 2000 companies looking for some details (CRN, Post code, address...)
    Last edited by damnation; 12-09-2016 at 08:07 AM.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    Did you download the sample workbook and try it?

  10. #10
    Registered User
    Join Date
    12-09-2016
    Location
    berlin
    MS-Off Ver
    Office365
    Posts
    4

    Re: UK Companies House API

    I have downloaded it, but it's just an empty sheet. I do not see the userform if that's supposed to be there?

    Edit: I managed to get the userform! Hooray. One step ahead. Thank you so much!
    Now... If I had a list with company names and wanted to find the company and then populate the neighbouring cells with the CRN, postcode, address etc, how would I go about that?
    Last edited by damnation; 12-13-2016 at 10:31 AM.

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    It isn't that easy, how would you deal with multiple results from a company name? But I digress, open a new thread for your question (you can include a link to here to help)

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: UK Companies House API

    Post removed.
    Last edited by AliGW; 12-13-2016 at 11:00 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    Ali, bit harsh, this is the tips and tutorials forum so the rules are a bit more lax here

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: UK Companies House API

    As this is in the Tips and Tutorials forum, I would have thought it was appropriate to allow subscribers to ask questions about it.

    Pete

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: UK Companies House API

    Sorry, chaps - did not realise it was in this section!

  16. #16
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: UK Companies House API

    Id just like to say that this is beautiful..........Ive just started looking at a project which involves CH data so this should be ideal starting point.
    If someone has helped you then please add to their Reputation

  17. #17
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    No problem, if you use it and add any more to the wrapper, upload it and I'll update my workbook with your additions

  18. #18
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: UK Companies House API

    hmmm I seem to be having a problem and apologies if it should be in a new thread (Im guessing not but no doubt fast gun Ali will delete if not valid )

    Ive created a key, added it to the constants and attempting searches but each time I get the following error

    "The server name or address could not be resolved"

    When I debug it steps through and correctly constructs the search query, the baseURl and qry variables hold the expected data but then fails in the .Send (hangs for 5 seconds or so before returning the error).

  19. #19
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    What happens if you copy and paste it into the browser? Sounds like you've misspasted your API key

  20. #20
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: UK Companies House API

    Ive checked and double checked the Key and its spot on.

    If I paste the query thats created into a browser
    https://api.companieshouse.gov.uk/se...&start_index=0

    I just get nothing, it flashes up in bottom left with "Establishing a Secure connection" but nothing else happens.

  21. #21
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    Hmmm, I've just tried it and it's working fine. Just to try and rule out some issues, try swapping:
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    In CompaniesHouseRequest

    I was being slack with the browser thing, it won't work since you're not authenticated

  22. #22
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: UK Companies House API

    you sir, are a genius.

    Much kudos to you.

  23. #23
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    No worries, it's a networky config thing at your work that's causing the issue (probably a proxy server)

  24. #24
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: UK Companies House API

    hmmm that would make sense it is a seriously secure network

  25. #25
    Registered User
    Join Date
    02-28-2017
    Location
    Birmingham
    MS-Off Ver
    7
    Posts
    1

    Re: UK Companies House API

    Hello everyone,

    I am new here and am impressed by the work you do here to help on a daily basis.
    I just stick on this topic as it seems to be the only one on the internet to tell how to extract companies house's data and to put it in an excel file.
    The problem is I am really not an IT expert, and when I open your file Kile, I cannot play the macro.
    Reading your comments i might need an API key but I do not know how it works either.

    Would you have a sort of a tutorial to make your program work?
    Not much details, just some clues, i'll check what to do on other websites.

    Thank you very much,

    Picolaud

  26. #26
    Registered User
    Join Date
    11-08-2012
    Location
    birmingham, uk
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: UK Companies House API

    Hi Kyle,
    You sir are amazing, this is awsome work. I got my api key and form worked for me. so thanks to you...
    I have a list of companies for which I have to download certain fields and i type them manually everyday, I am wandering if there is any way from company number we could retrieve information like company name, incorporation date or accounts due by date etc on the same line in next columns of worksheet not in a form that would make my life so much easier. don't know even if this is possible, any help is much appreciated.
    Many Thanks

  27. #27
    Forum Contributor
    Join Date
    06-07-2013
    Location
    Bournemouth
    MS-Off Ver
    Excel 2003
    Posts
    158

    Re: UK Companies House API

    Hi all

    I have taken one of Kyle's tools and have managed to tweak it a little to collect a few extra fields.

    I am now trying to collect the filing history to collect the transaction_ID for the latest accounts a company.

    I can pull in the json file and see the data in the locals windows. From what I can tell the lowest item number is the latest record. I only need this record not the historical data.

    Has anyone had any success in pulling just the latest record from a JSON from Companies house?

    For some reason I cant upload my file

    Appreciate any advise or help.

    Thanks

  28. #28
    Registered User
    Join Date
    07-30-2018
    Location
    Monmouth, Wales
    MS-Off Ver
    365 Business
    Posts
    1

    Re: UK Companies House API

    Thanks for this Kyle. Very impressive. I would be interested in the script to generate the classes if it's not too much trouble.

    Cheers

    apostleit

  29. #29
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    This was a while ago and I'm not sure I still have it, I'll have a look tomorrow

  30. #30
    Registered User
    Join Date
    03-08-2016
    Location
    Newport
    MS-Off Ver
    2010
    Posts
    17

    Re: UK Companies House API

    Can anyone help me, I'm getting a run-time error 429 when I open the userform.

  31. #31
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    You need to post the line of code that you get that on. Are you using a Mac?

  32. #32
    Registered User
    Join Date
    11-14-2019
    Location
    norwich
    MS-Off Ver
    365
    Posts
    1

    Re: UK Companies House API

    I know that this post is a few years old now but I am hoping someone out there can help me!

    This is basically EXCATLY what I have been trying to build and have been met faliure at every step. I addedmy API key but when I try open the userform I get the below;

    Name:  Capture.PNG
Views: 1944
Size:  5.5 KB

    Please can any help?

    Thanks

  33. #33
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    That's a bit of an odd error message and I suspect that your version of Excel may need repairing, however we'd need a bit more to go on.

    What version of Excel are you running?
    Are you using Excel 64 or 32 bit?
    Which version of windows are you using?
    Is your windows version 64 or 32 bit?
    Do you have any missing references?

  34. #34
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    365
    Posts
    2

    Re: UK Companies House API

    Hi Kyle123,

    I have the same issue as asjmoran.

    Running the latest version of Excel office on a 64bit program and windows 10.

    The exact same workbook works on my laptop though (cant remember the version, will check and edit the post). So it doesn't seem to be an issue with workbook. Have also tried reinstalling excel and that did not help.

    Any ideas? Could it be my firewall?

    Thanks,

  35. #35
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: UK Companies House API

    It’s likely because you’re using 64-bit Excel. If I recall correctly, the Json is being parsed by a library that’s not available on 64 bit Excel

  36. #36
    Registered User
    Join Date
    12-20-2018
    Location
    London
    MS-Off Ver
    365
    Posts
    2

    Re: UK Companies House API

    Thanks!. Any way for me to easily amend it to work on 64bit?

  37. #37
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: UK Companies House API

    Quote Originally Posted by asjmoran View Post
    I know that this post is a few years old now but I am hoping someone out there can help me!

    This is basically EXCATLY what I have been trying to build and have been met faliure at every step. I addedmy API key but when I try open the userform I get the below;

    Attachment 649666

    Please can any help?

    Thanks
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  38. #38
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: UK Companies House API

    Hi Ford,

    we've had this discussion in other threads. As this thread is in the Tips & Tutorials forum, it is perfectly reasonable to allow other contributors to ask questions about it - see Post #14.

    Pete

  39. #39
    Registered User
    Join Date
    10-26-2018
    Location
    UK
    MS-Off Ver
    2016
    Posts
    20

    Re: UK Companies House API

    Hello!

    Is there any way to search for companies via name and it pulls the first result from the search into a cell?

    So i'd search for example "tesco" and it would pull back "Tesco PLC" and "00445790"

    Appreciate the work you've done
    Last edited by joeburg; 03-26-2020 at 09:38 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. House Numbers
    By kumarin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-07-2015, 09:16 AM
  2. New in the house!
    By Machme in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 03-14-2015, 08:21 AM
  3. VA in the house!
    By shingik in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-27-2013, 07:31 PM
  4. Replies: 14
    Last Post: 07-28-2013, 10:44 AM
  5. The Dog House!
    By Mordred in forum The Water Cooler
    Replies: 10
    Last Post: 06-30-2011, 06:19 PM
  6. Using info to build house
    By Zetmandu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-28-2011, 07:50 PM
  7. New MVP in da house?
    By Domski in forum The Water Cooler
    Replies: 21
    Last Post: 08-20-2010, 01:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1