I got this idea from Tony Hirst’s great blog. The OpenCorporates API gives rich information many companies. Using Google Refine to reconcile, it cleans up many sources of data to a queryable datasource.
So here is an initially very simple entry for the rest-Excel library that takes a query and populates a worksheet with information on all the companies registered similar to that query. Here’s the first few lines of a query on ‘Google’
I used the rest Explorer to see what data this kind of response this thing returns, and picked a few coumn headings to be populated as above. The library entry is pretty straightforward and looks like this
1234567 With .add("opencorporates reconcile").add "restType", erSingleQuery.add "url", "http://opencorporates.com/reconcile?query=".add "results", "result".add "treeSearch", True.add "ignore", vbNullStringEnd With
and the code to execute the query looks like this
12345 Public Sub testOpenCorporates()generalQuery "opencorporates", "opencorporates reconcile", _InputBox(prompt:="Enter your openCorporates search query", _Title:="openCorporates API query:results to the opencorporates worksheet")End Sub
I’ll be returning to this topic in the future as I dig a little more into this API. This is example is in the cDataSet.xlsm workbook and can be downloaded from the ramblings site.