Problems using Basic authorization with Streams

#1

I have created a temporary application to get a streaming key, to access

Streaming API: Basic company information stream (company-information.service.gov.uk)

following the instructions for Streaming given at

Streaming API: (company-information.service.gov.uk)

I have used XHR in Excel and with JavaScript, but I can’t get through.

EXCEL

Sub Button1_Click()

Dim xmlhttp As Object
Set xmlhttp = CreateObject(“MSXML2.serverXMLHTTP”)
Dim myurl As String
myurl = “https://stream.companieshouse.gov.uk/companies”
skey = “f8da6601-6121-49e7-9b1f-8e3d7f726b57”
key64 = “ZjhkYTY2MDEtNjEyMS00OWU3LTliMWYtOGUzZDdmNzI2YjU3Og==”
xmlhttp.Open “GET”, myurl, False
xmlhttp.setRequestHeader “Authorization”, "Basic " & key64
xmlhttp.Send
rsp = xmlhttp.responseText
[a4] = rsp

End Sub

JS

xhr =new XMLHttpRequest();
url = “https://stream.companieshouse.gov.uk/companies”;
skey = “f8da6601-6121-49e7-9b1f-8e3d7f726b57”;
key64 = “ZjhkYTY2MDEtNjEyMS00OWU3LTliMWYtOGUzZDdmNzI2YjU3Og==”;
xhr.open(“GET”,url, false);
xhr.setRequestHeader(“Authorization”, "Basic " + key64);
xhr.onload=()=>{
rsp = xhr.response;
out.innerHTML = rsp;
}
xhr.send();

#2

Not sure whether you can do this in Excel, nevertheless, Is that a typo there with a missing “h”? url = “https://stream.companieshouse.gov.uk/companies”;

#3

I’m only allowed 2 links per post (because I’m a newbie here), so I removed the ‘h’ from 'h*tps to bypass the checker.

#4

Do you get an error? What http response code do you get and what (if any) body do you get?

Just wondering what exactly are you wanting to do here? I don’t use the stream API myself but as I understand (or as Companies House say in their introduction) you need to:
a) establish a long-running http connection
b) stay connected as long as possible: “Conceptually, you can think of this as downloading an infinitely long file over HTTP.”

So the Excel code (VBA?) or javascript would need to be able to:
a) keep the connection open - so you might need to alter built-in timeouts. If you kept disconnecting and reconnecting Companies House say they may block you.
b) respond to streamed information as it arrives (before a connection ends - as Companies House aim to avoid ending this). I don’t know about Excel. I’ve not done that with javascript XMLHttpRequest myself and I’m not sure that’s possible. However it does look like streams may be available when using the fetch API so this might work for you - again it’s not something I’ve done personally.

There might be 3rd party libraries to help with that also - especially if you were using javascript in a server environment e.g. Node / Deno.

Good luck.

#5

JS gives a CORS error:

Access to XMLHttpRequest at 'https://stream.companieshouse.gov.uk/companies' from origin 'null' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource.

Excel says:

{“error”:“Invalid Authorization”,“type”:“ch:service”}

But I can’t see why a public API would be CORS protected, and Excel seems to say my username (API key) is wrong, but I think I have followed the instructions properly.

#6

Update: the “origin null” suggests you’re maybe using localhost (or haven’t registered your js domain with Companies House) so see this thread (or similar ones):

As always I recommend starting from basics. Check your actual API key / authentication by making a http request with the simplest tool you can e.g. curl:

curl -v -u MY_API_KEY_HERE: "https://stream.companieshouse.gov.uk/companies"

(Note the trailing colon - this is because there is no password, the username is the API key).

If you get blocked here you either have an invalid API key OR it may be Companies House is objecting to the IP address / domain. You need to ensure that in your Application details you have set IP addresses / javascript domains for where you’re accessing from. (I think the javascript domains may be the issue for you). You can update your application here:

If you’re doing this from a localhost environment this is possible - search this forum for instructions on a workaround for doing that.

I have no idea about doing this with Excel but again if you search the forum or google you may find some help. People have managed to make this work with (what appear to be) other “local” applications e.g. Microsoft Power BI.

#7

I got a partial solution, by abandoning stream and using RESTapi instead.

I logged on with Excel and curl (Documents not downloading, invalid ID - #7 by csmith).

I also got JS/XHR to work - load the client-side file to your server and enter the domain into the ‘create key’ rest section under JavaScript domains.

Thanks for everyones help!