hmmm, 25 is the default and now looking closer, though the subsequent requests have an incremental offset included, it's the same top 25 IP records time and again, 526 times, where the url line on the 56th map clearly asks for an offset of 13750, after which the rest goes into the void

ttps://secure.worldcommunitygrid.org/api/members/deltavee/results?code=accountkeycode=250&offset=13750&ServerState=4&SortBy=SentTime

Specifying 250 records (per API max), and doing some diagnostics, getting, per MSDN

Return Value

A XlXmlImportResult value that indicates the result of the method.
Remarks

This method returns one of the following XlXmlImportResult constants:

xlXmlImportElementsTruncated The contents of the specified XML data file have been truncated because the XML data file is too large for the worksheet.

xlXmlImportSuccess The XML data file was successfully imported.

xlXmlImportValidationFailed The data being imported failed schema validation, but was imported anyway.

The url composit is right, the sort order is right, the server state filter is right, both items at end of the url, yet interspersed one or the other fetch only contains 25 records, and then always the top 25, not those as requested by the offset.

Doing some benchmarking, the average fetch run of 15*250 results &ServerState=5, run in 17 seconds, best in 14 seconds, which is great meaning 250 records are read in, transmitted and imported in under 1 second, successively, but the momentary average fetch time of 11 pages for serverstate=4, In Progress, is 22 seconds.... identical script, same procedure call, with just a different url suffix &ServerState=4&SortBy=SentTime instead of &ServerState=5&SortBy=ReceivedTime

Something is getting truncated maybe, so looked at string lengths of the urls... 167 up to 10000 records, then 168 where MS Office insider revealsmy emphases
So now I do a scripted direct inject of the url instead of using databinding.refresh, which is awfully nicer too in code... reuse the previously formatted connect queries, and build in a dowhile loop, because it (speculative), something is not keeping up from my mountain range to into the WCG hosted data center on the Toronto flats, randomly or for all serially sent 11 requests, to repeat for In Progress only... very very weird.

Thnks for investigating. Yes, thought there was a limit, but ServerState 4 or Serverstate 5, when using SorBy=ReceivedTime or SortBy=SentTime is in fact 4 char shorter, the various researches hitting on that 255 string length history, to which we're not getting even close... crtrl characters tend to be making strings longer, but there are only few.

Unfortunately, it's not repeatable in a forced way, the received data is just that, 25 records off the top. One of the test passes today had the seventh 250 offset return going bad, so from &offset=1500. The symptom is that the return xml shows there was no filtering, so e.g 5300 records, 25 fetched instead of an expected 1600 with 250 fetched.

It's almost time off the day that matters... the schedule starts running Results and In Progress fetches at 00:06:15 UTC (when I'm shuteye), to get a snapshot through that 6 minute spilltime, then when in the morning stepping through fetching My Contribution > Archive prior period credited results, fetching device statistics and finally a first pass of new stats period results & in progress to peruse, it's then things fall over, and remain so, so leave it alone, and in afternoon things seem to have returned to normality, bar the occasional 250 set turning 25, without the serverstate filtering i.e the top selection of all that's on the Result Status pages. The noted coding change, direct injecting with .XmlMaps("InProgress_Map" & I).Import url:=rURL , where rURL is the preformatted url string and I is the page counter, so a page var value of 6 becomes 1500, the limit=250 is hardcoded. When running the script and outputting rURL to the 'immediate' window all list proper, incrementing the offset correctly with each pass, looking like this, username and account key redacted:

ttps://secure.worldcommunitygrid.org/api/members/username/results?code=accountkey&format=xml&limit=250&offset=0&ServerState=4&SortBy=SentTime
ttps://secure.worldcommunitygrid.org/api/members/username/results?code=accountkey&format=xml&limit=250&offset=250&ServerState=4&SortBy=SentTime
ttps://secure.worldcommunitygrid.org/api/members/username/results?code=accountkey&format=xml&limit=250&offset=500&ServerState=4&SortBy=SentTime
ttps://secure.worldcommunitygrid.org/api/members/username/results?code=accountkey&format=xml&limit=250&offset=750&ServerState=4&SortBy=SentTime
ttps://secure.worldcommunitygrid.org/api/members/username/results?code=accountkey&format=xml&limit=250&offset=1000&ServerState=4&SortBy=SentTime

front h removed to get the strings to show.

Cant's see anything malformed being sent. The urls are actually generated in a For Next loop, just inserting the incremental offset value. The big question being, what goes across the Transatlantic trunk cable and what arrives at your end. The return feedback I get is xlXmlImportSuccess, i.e. sent a url, received an xml response, properly formatted. If I malform the url, the script hangs and a screen pops up to say "can't parse", suspecting this is some kind of feedback from the server to my client.

Since you can see the logs of my tests, can you see the actual urls that are read in?

WGet utilization within Office, talking to the w3w via IE is next on my list of things to learn (Office does not speak with other browser engines). A first trial actually allowed me to open a IE frame inside an Excel sheet... live browsing, picking and choosing what's wanted, but that's by hand... now the scripting of same to be mastered... macro recording ends right at the doorstep of the IE shell :(

FTM, the new code works faster for In Progress (In Progress used to always be faster, but since this it's slower to get the pages filled... a sign something in your server room is/was working harder at filling the requests for the ServerState=4

Yes - and they are just like the above that you indicate so there is no truncation occurring.

Great. From my end, (to keep it spatial), no new interdimentional vortex has opened up since last I wrote.... highly stable, except for the occasional missing result due the 'liveliness' of the Result Status page transacting, but think I've got a strategy for this. Starting with asking 250, offset=0, the next pass will ask for 250 again, but from offset=240, the next 250 from offset 480 and so on. This will lead to duplicates in the initial cache, but that was already handled... the first one with the same result name is taken, and any additional marked double. Since the sort is received-time, descending, putting the newest to top, might want to pull the top 250 last, since these would catch any that changed while working on page 2 to nn sequentially, though shooting the requests in parallel has crossed my mind... probably will choke something.

Typically the tipping point seems to lie around 1000 results / 12 hour stats cycle. Less and hardly see any missing, above and fetching is not fast enough. Fact is, if a result is reported/validated (that cycle takes between 5-10 seconds) while churning through 15-20 at extremes 60-70 pages * 250 when there's days of very shorts, that new record goes to top, so by page 10 or whatever, a result caught on page 9 is again caught on page 10, but the doubles check handles that. Opposed if a PV result is on page 10 (record 2251-2500) and get's validated while working on page 9, it does not get caught in any state, i.e. the snapshot is incomplete... not on results, not on in progress. The period end cycle during archiving actually does a new pass and catches any that were missed, a post-archive-compare-to-archive routine is called that looks at timestamps and identifies these. Sounds all complicated, but the object is to get a result archive that adds up per period to the exact total of the statistics history. For now, the reading in of the 250 sets and cycling through the for-next slowness is the root cause. It seems to work well though for a single account up to 17500 results... few that have this, and few in this realm could probably be concerned.