No, it does fail. Even the first I posted failed after selecting OK. Same error as you noted.
As for the code, surround you code with three back ticks. You are posting curly quotes which cause errors.
ah darn, I guess I report a bug on this function then? Or is this a feature request ? I guess a settings parameter is needed to modify how many requests the Webservice sends ( I read somewhere it is 5).
Do you know any other way to retrieve the content from that link?
Not certain I have anything on this. Will look later & post if anything is found. No breath holding, please.
@Vitus_Hawkridge, do you have an account with interactivebrokers? They have a RESTful API. I don’t know what access comes with what accounts. If you are really “getting into the thick of it” tying together the RESTful API and the UNO/LO API in Java might be a more robust (if not especially fast) solution.
Hi Joshua, yes, I do. The flex query has advantages - for example being read only and is relatively straight forward. The Rest api I think is only directly to the trading tool itself (trader workstation) and that is not good enough. the TWS only stores data for one week, the rest needs to be taken via the flex query…
Do you know any other way to do http calls? I have been googling for hours with no avail. I see only these two possebilities:
-
api - VB6 -- using POST & GET from URL and displaying in VB6 Form - Stack Overflow
but i guess this only works for windows. -
Somehow use Basic to access the terminal and then Curl the stuff.
-
Use Excel in Windows ;(
Likewise, for Windows, there is, apparently, How to get the stdout result of a Basic "shell" order?? Not sure what all technology is getting mixed in that one.
I checked, and UNO’s SystemShellExecute returns void. So to use curl either you’d need to find another UNO call, or you’d have to have bash write out to a file and read it, etc.
This interests me! If you think you might use curl, ask a new topic on “Getting return values from Linux system calls” or something…if @Ratslinger et al don’t pop up an answer right here.
Have found this code which works:
.
Sub Main
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
XML_String = svc.callFunction("WEBSERVICE",array("http://www.lipsum.com/feed/xml?amount=2&what=paras&start=Yes"))
Lipsum = svc.callFunction("FILTERXML", array(XML_String, "/feed/lipsum" ))
Print Lipsum
End Sub
Also note this post > Documentation/Calc Functions/WEBSERVICE
.
In the working example posted above there is a parameter of amount=2 which retrieves 2 paragraphs (not certain of that meaning) and if modified will retrieve more of less depending upon the setting. Also not knowledgeable with that next line using FILTERXML
.
Not really familiar with all this but possibly this may be what you need.
.
Edit:
Just found where I got the code from > Use of WEBSERVICE within a macro in LibreOffice 6.0.1.1
@Ratslinger unfortunately I got the code originally exactly from this source and modified it. The amount = 2 is simply the length of the returned string on this website not the number of tries Webservice requests the data.
The code works in this case as there are no “too many requests” on this link and it does not matter that Webservice polls a few times. In my case however Webservice fails because it requests too often in a short period of time…
Is there a way to analyze the source of the webservice function and perhaps modify it?
Each call to WEBSERVICE() does three requests to the web server: OPTIONS, HEAD and GET. Only the GET request obtains actual data, if the server counts the OPTIONS and HEAD requests as API data access it’s broken. Some servers also deliver data already with the OPTIONS request but that is equally broken. Some may even do with HEAD but that’s utterly broken.
See also
1 Like
@erAck is there a way to only use the get part of the webservice? or a delay between the three calls? Or any other way to fix this. The webservice i use blocks traffic after the second call I just realized - hence always the failure
No.
You don’t need a terminal for this.
We call Curl from Basic:
Shell "curl", 0, arg, true
where arg is curl options string.
Don’t forget to provide an option to redirect stderr (–stderr), stdout (-o), silent options(-s -S).
2 Likes
let me try this one thx a bunch
Sub main
response = Shell (“curl”, 0, “https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=583344&v=3”, true)
Print response
End Sub
Hmm do i overlook something? I have tried in the shell
and it works, but the above code does not.
We are waiting for the second attempt. 
Redirect streams to (temporary) files, and then read the contents of those files (if those files appeared after the call to curl).
We have the corresponding working macros with call to curl, but they use a lot of our auxiliary utilities.
Hmm I think I am just no familiar enough with these options, I tried various ways but all of them returned 0 for success, but where is the output?
I tried putting -s -o -stderr in your version @erAck but also tried this code below, all with the same result.
Could anyone of you perhaps help me out with this please? Sorry to bother all of you, I would not manage without your help.
url = “https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=583344&v=3”
response = Shell (“curl -s -S -o “”” & url & “”"")
I’ll try to make an example when I have time.
1 Like
An example is in the attached file. You need to run macro Test. Below is the text of the macro and the header of main function.
Option Explicit
Option Compatible
Sub Test
Dim url As String, retval As String, result As String
url="https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=583344&v=3"
retval=CurlCall(url)
If Left(retval, 1)<>"*" Then ' Not error
result=fileToStr(retval)
Msgbox result
End If
End Sub
' lang:en
' Call the Curl utility.
' - arg: utility argument (options).
' - postData: sets the data (body) for the POST request. If the first character is "@", then it is interpreted as a file name.
' - out: redirect stdout output for curl. The file name or only the extension can be specified (will be redirected
' to a temporary file with this extension). If you do not need to redirect, then specify an empty string or "-".
' If the out parameter is omitted, then redirect to a temporary file.
'
' Returns:
' On normal completion, the name of the file with redirected output stdout, or an empty string if no output was redirected.
' `*1`: Error calling curl: The file specified by the --stderr parameter was not created.
' `*2`: Error: The file specified by the postData parameter does not exist.
' `*filename`: the name of the file containing the error log with a "*" at the beginning (in URL format).
Function CurlCall(Byval arg, Optional postData as String, Optional Byval out) as String
CurlCall.ods (13.0 KB)
1 Like
Holy!!! Thank you so much. I would never ever have managed that. Looking through all of your code I see you create temp folders, temporary files for errors, return value etc just to retrieve the shell output and then save it back. This is genius.
Thank you so much it works!.
Final question to this: Do I need to delete these temporary files? I am loading them a lot (e.g. 1/min) and they might be xml files of up to 1mb. Does the
The links I have provided have now been switched off as they exposed some private data. If you use them now the “correct” response will be :
Fail
1015
Token is invalid.
The incorrect response is still “fail , Error 1018, too many requests”. Thanks to everyone and especially @sokol92 for your awesome help. This one can be closed.