HTTP Request / Webservice too many requests / too fast?

English
, , ,
#1

Hello community,

I need to request data from a url with an xml response but from within a macro. I have found nothing but the webservice - see example code below. Perhaps you have an alternative solution to the webservice? If not:

URL in question: https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=664945&v=3

sub main
svc = createUnoService( “com.sun.star.sheet.FunctionAccess” ) 'Create a service to use Calc functions
XML_String = svc.callFunction(“WEBSERVICE”,array(“https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=664945&v=3”))
print XML_String
end sub

If I use it in a browser I receive a success response - however if I use it in the macro I get a fail with Error 1018, this error means “too many requests”. The server on the other side is quite “lenient” and allows requests every few seconds, but the Webservice seems to request very often even though it is one function call. Does anyone know what to do here?

Is it possible to limit the webservice request to try once and then have some code to wait a progressive period of time until the next request is sent?

Thank you very much in advance

Calc Macro: How to store key value pairs
#2

My first suspect: When you do this in a browser, are you logged in? If yes, how will your macro log in?

#3

No login required. You can try the link, it should work for you in the browser, but not as a script.

#4

Hello,
Have just tested this from Linux Ubuntu 20.04.4 using:

Version: 7.3.1.3 / LibreOffice Community
Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

.
and slightly modified code:

sub main
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" ) 'Create a service to use Calc functions
XML_String = svc.callFunction("WEBSERVICE",array("https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=664945&v=3"))
print XML_String
end sub

.
with this result:

Screenshot at 2022-04-03 13-33-24

#5

Hi Ratslinger,

thank you for taking a look into this.
I do not see the difference of your code and I am not sure you get the right result. Could you try:

print svc.callFunction(“FILTERXML”, array(XML_String, “/FlexStatementResponse/Status” ))

If you receive a fail then you have most likely the error 1018 again which means too many requests. If you receive success then your version works.

#6

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.

#7

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?

#8

Not certain I have anything on this. Will look later & post if anything is found. No breath holding, please.

#9

@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.

#10

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:

  1. api - VB6 -- using POST & GET from URL and displaying in VB6 Form - Stack Overflow
    but i guess this only works for windows.

  2. Somehow use Basic to access the terminal and then Curl the stuff.

  3. Use Excel in Windows ;(

#11

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.

#12

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

#13

@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?

#14

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
#15

@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

#16

No.

#17

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
#18

let me try this one thx a bunch

#19

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

curl “https://ndcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=280014687756071353099584&q=583344&v=3”

and it works, but the above code does not.

#20

We are waiting for the second attempt. :slightly_smiling_face:
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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.