getSharePrice_1 is taking things easy for Christmas

[csearle]

Good morning!

For some reason getSharePrice_1 became really quite slow a few days ago. Following a discussion we had here at the end of July I'd started using it in my spreadsheet. Currently the function is called for my 31 shares. It now takes eight minutes to get through them all; before it would be just a few seconds.

I've single-stepped through it and determined that the line causing the (new) delay is this one...

Code: Select all

Set theMatches = regexp.Execute(hrow.Cells(1).innerText)

A quick Google suggests that there might now be some catastrophic backtracking going on. If so, why this should have suddenly become worse is beyond me.

I have no experience with this regular expression function, regexp. Does anyone know why its behaviour might have changed and/or how I might speed it up again?

Thanks,
Chris

PS The regular expression it is using is:

Code: Select all

regexp.Pattern = "(\D?)(\d*,?\d*\.\d*)(.)"

and it is, I imagine, searching through some HTML returned by shareprices.com

[csearle]

An afterthought: if shareprices.com changed their HTML format a few days ago in such a way that the search still works but has to backtrack much more to find what it is looking for then this might explain the new delay. C.

[kiloran]

Good morning!

For some reason getSharePrice_1 became really quite slow a few days ago. Following a discussion we had here at the end of July I'd started using it in my spreadsheet. Currently the function is called for my 31 shares. It now takes eight minutes to get through them all; before it would be just a few seconds.

[snip]

Thanks,
Chris

Chris, I just tried simple_price_scrape_v2-1.xls with 35 shares (mixture of FTSE350, non-FTSE350 and an invalid share symbol), and it rattled through them in a matter of 2-3 seconds. Using Excel 2016 on Windows 10, on two different laptops.

getSharePrice_1 is used for shares not in the FTSE350.

Are you using version 2.1?
Looks like your problem is with a non-FTSE350 share. Can you send me the list of shares you are using

--kiloran

[csearle]

So I have tried substituting various different search patterns and various different strings to search. It seems, for whatever reason, the regexp.Execute function is now super-slow regardless of what it is searching for and through!

Are you using version 2.1?
Looks like your problem is with a non-FTSE350 share. Can you send me the list of shares you are using

I am just using your getSharePrice_1 function in my own spreadsheet. Some of the shares I'm using it on are

ABDN.L
ADM.L
AV.L
AZN.L
BA.L
BATS.L
BDEV.L
BHP.L
BLND.L
BP.L
BT-A.L
GSK.L

but I think that is irrelevant because the datascrape works extremely fast, it's just the subsequent extraction of the share price locally on my PC that is on a go-slow!

Chris

[csearle]

My current suspicion is that there was a Microsoft 365 automatic update on 12 December 23, which might have changed something. I'll see if I can revert to a previous version with a view to confirming/allaying this suspicion. C.

[csearle]

Realise I'm blathering on a bit here, but it turns out that my version of Excel was automatically updated on November 29th. The December 12th update, for some reason, either didn't affect Excel or didn't happen. Anyway I'm now in the process of downloading/installing the December 12th update in the rather vague hope that something inscrutable happened to regexp and that it was fixed in December 12th update. I'll report my findings. C.

[csearle]

Well that was an almighty palaver. Now on latest Microsoft 365 update. No change to super-slowness. The search continues. C.

[csearle]

As a workaround until the problem is found I've written a variant of getSharePrice_1 that parses the string using other VBA functions, ones which still work very quickly. C.

[kiloran]

Might be worth checking your VBA references (DEVELOPER/TOOLS/REFERENCES)



--kiloran

[csearle]

Might be worth checking your VBA references (DEVELOPER/TOOLS/REFERENCES)

Thank you.

Oooh, mine looks slightly different!

I own (or am legally permitted to post) the content of the image above.

[kiloran]

Might be worth checking your VBA references (DEVELOPER/TOOLS/REFERENCES)

Thank you.

Oooh, mine looks slightly different!

I own (or am legally permitted to post) the content of the image above.

I tried with your references, Chris, and it works fine.

Clutching at straws here.... could it be your antivirus? Can you try the VBA with your antivirus disabled?

--kiloran
edit.... sorry, I'm talking gibberish here. If your process using VBA functions rather than regular expressions works, I doubt it's an antivirus issue

[csearle]

Understood. Thanks for trying kiloran. Because this regexp.Execute function has the potential to run for longer than perhaps expected (because of the catastrophic backtracking that can happen depending upon both the search pattern and the string searched), most if not all Google hits I'm getting are about this effect rather than whatever I'm experiencing. As my delay occurs even when calling it with very simple searches I'm fairly sure this is not my issue. C.

[kiloran]

Understood. Thanks for trying kiloran. Because this regexp.Execute function has the potential to run for longer than perhaps expected (because of the catastrophic backtracking that can happen depending upon both the search pattern and the string searched), most if not all Google hits I'm getting are about this effect rather than whatever I'm experiencing. As my delay occurs even when calling it with very simple searches I'm fairly sure this is not my issue. C.

You are using the code from "simple_price_scrape_v2-1.xls" in your own spreadsheet.

What happens if you download a fresh copy of simple_price_scrape_v2-1.xls from https://lemonfoolfinancialsoftware.weeb ... crape.html and, without making any changes, just update the prices in the SharePrices tab? Does that also run slowly? For me, it takes about 5 seconds.

--kiloran

[csearle]

Oooh, good experiment, I'll have a go after work. C.

[csearle]

What happens if you download a fresh copy of simple_price_scrape_v2-1.xls from https://lemonfoolfinancialsoftware.weeb ... crape.html and, without making any changes, just update the prices in the SharePrices tab? Does that also run slowly? For me, it takes about 5 seconds.

Sorry it has taken so long to reply.

I downloaded that and it took just over 1s to complete. I looked at the code and that uses your Yahoo() function rather than getSharePrice_1() and so it never hits the regexp function call.

I think I'll just tweak my main spreadsheet to not use the regexp function.

Thanks,
Chris

[kiloran]

What happens if you download a fresh copy of simple_price_scrape_v2-1.xls from https://lemonfoolfinancialsoftware.weeb ... crape.html and, without making any changes, just update the prices in the SharePrices tab? Does that also run slowly? For me, it takes about 5 seconds.

Sorry it has taken so long to reply.

I downloaded that and it took just over 1s to complete. I looked at the code and that uses your Yahoo() function rather than getSharePrice_1() and so it never hits the regexp function call.

I think I'll just tweak my main spreadsheet to not use the regexp function.

Thanks,
Chris

That's confused me a little, Chris (I get confused very easily at my age!)

The document "simple_price_scrape_v2-1.xls" has two main tabs:

The SharePrices tabe gets prices from the SharePrices website, using the macros "getSharePrices()" and "getSharePrice_1", which both use regexp

The Yahoo tabe gets prices from Yahoo, using the macro "Yahoo", and does not use regexp.

When you say you downloaded the document and it ran in just over 1s, was that using the SharePrices tab or the Yahoo tab? If it was the Shareprices tab, that suggests that your problem was with your implementation of the code in your spreadsheet. If it was the Yahoo tab, it doesn't tell me if the code in the Shareprices tab is as robust as I would like.

--kiloran

[csearle]

Ah, sorry, in my haste I just saw a button and clicked it (I'd be a useless president with that red button on my bedside table).

Yes the SharePrices one worked fine, about 5s too. So I single stepped through and noted that the regexp returned pretty much immediately. This is not the case in my spreadsheet even though the regexp.Pattern parameter is identical. I will need to have a look at whether the argument passed to regexp is different for any reason. (At the moment I've just replaced the whole thing with your Yahoo() function so the problem is gone. )

C.