Handling multiple requests

[KD33]

Hello, I am working on an add-in that needs to be able to make multiple requests at once. The idea is:

Get user input from cell -> Package this in a web request -> Our backend sends back a response based on what's sent in the web request. I have no trouble setting up the request response cycle, but am having some issues with latency, and am wondering if anyone has suggestions so that I can make about 1000-1500 queries at a time.

I thought about making the request, then saving the response content to the excel file, but the problem with this is that the backend data updates on an unpredictable schedule. Therefore, a user using this saved response could end up with old data in their spreadsheet.

Thanks

[zgrose]

1000 requests at a time? Are you developing a DDoS attack app?

[KD33]

No. Forgive me if that seems a bit unrealistic, it is a specification of the person overseeing the product. The idea is that a user should be able to get updated data for their stocks from our backend. My boss has some experience using bloomberg excel api, so I assume that is where he is getting the idea from.

As it is right now a user can make multiple requests, but this slows down once the number of queries increases. I’m looking for a way to reduce this time. Thanks

[Sophist-UK]

You should rewrite your API so that you can collect the 1,000 stock ids and submit them in a single request, and return the results in a single response. This will result in far better performance.

[Sophist-UK]

But if you have to submit requests in parallel, this is possible using the async approach.

You may need to manually apply the code in #206 if you want to use the Async code I developed as this has not been merged by the code owner.

[KD33]

Appreciate the feedback Sophist. I was able to run the requests in parallel like you suggested, but it raises another concern. I'm assuming that in order to get this to work, the client need the WinHTTP Services reference selected. I want this add in to be ready to go out of the box, and not require the user to have to do configuration. So now I ask, am I wrong in my assumption? If not, is there a way for me to turn this reference on with my add-in?

Thanks

[Sophist-UK]

I don't know - but the reference is turned on in the VBA project i.e. the Excel spreadsheet or Excel Addin.

So distribute it as one of these and you should be OK.

[KD33]

Got it to work.

[Sophist-UK]

Did you make your API support multiple stocks in one call - believe me, in real life over a slow connection populating 1,000 stock details using 1,000 calls will be unbelievably slow. So if 1,000 is your design point, your API should support at least 100 or 200 stocks in one call.

[timhall]

Hi @KD33 I'm sure others will have the same question, care to elaborate on your final solution / include some sample code? (@Sophist-UK Thanks for helping out here)

[KD33]

@Sophist-UK we're using NodeJS, so my thought was that it would be able to handle multiple incoming requests at once due to it's asynchronous nature.

@timhall gladly, although I found myself running into another problem

The function that makes a request to our API
`Function ConnectToAPI(ID)

Dim Request As New WebRequest
Dim Client As New WebClient
Client.BaseUrl = "http://www.endpoint.com"

Dim Wrapper As New WebAsyncWrapper
Dim Wrapper.Client = Client
Dim Body As New Dictionary
Body.Add "ID", ID
Set Request.Body = Body
Request.Method = HttpPost

ConnectToAPI = Wrapper.ExecuteAsync Request, "CallbackFunction"

End Function`

Function CallbackFunction CallbackFunction = Parsed Data End function

What I would like to be able to do is have the response from my API parsed in the callback and sent back to the ConnectToAPI function, but this isn't the case. I checked that the callback is receiving the response (it is), but my guess is that the response from the callback isn't ready by the time
ConnectToAPI = Wrapper.ExecuteAsync Request, "CallbackFunction" is run.

As for why I need the parsed data sent back to the original function, I'd like for the function to be callable from a cell in Excel. That way a user can have in their cell =ConnectToAPI(ID) and it prints out the parsed data in that cell.

What I tried to do is pass the address of the cell calling the function down to the callback, then putting the parsed data in that cell address. However, this doesn't allow the user to F9 refresh the function to get the most recent values (a constraint placed by my boss).

[timhall]

1. Nothing should be returned from ExecuteAsync as it's handled async and ExecuteAsync returns sync
2. From what I can tell, Excel explicitly restricts updating cells in user-defined functions asynchronously. There may be ways around this (I haven't found one), but it sounds like you ran into another issue with F9 refresh

Thanks for the details. I've looked into ways of creating a wrapper for running multiple requests at once that handles the asynchrony for you, but haven't had the chance to look at it in a while.