HYPTUS v11.31 Run-time error 5

[Itsallaguess]

I have a strong suspicion that Itsallaguess will have some views on all of this

Hey, I'm on Day-2 of my 'No-look November', so this isn't affecting me! :O)

Joking aside, I'm sure we'll find a solution to this, so that shouldn't worry anyone, other than the fact that it's a bit of a pain for us at this time.

I'm actually more excited that we've got the original anc1 back posting, and I'd like to take this opportunity to once again offer my heartfelt gratitude to him for getting me into the whole Excel-VBA scene all those years ago, as it's been a tremendously interesting area for me to work in, both personally and professionally, since that time.

The core price-scrape in the HYPTUSS tool has always been based around anc1's original code, and I've always mentioned this whenever I've had the opportunity to do so, but this feels like another great chance to just offer my personal thanks to him whilst he's covering this thread.

Thanks for everything anc1/eventide! :O)

Cheers,

Itsallaguess

[James]

Just for reference, I'm getting the same thing, but I'm on an older version: 10.94
I get the run-time error 9: subscript out of range error.
worked okay yesterday, broke today.

[eventide]

It also works for me, though I have not yet checked how many shares it can pull at once (the previous method allowed 199 or 200 at a time).
The previous method gave us "Last Price" (for better or worse). The new method seems to give us 3 options:

"bid":220.95,
"ask":221.05,
"regularMarketPrice":221.0,
(those are for VOD.L)

Hopefully many more fields available

I've now got a basic version working with many hundreds of tickers, using my batching routine of 200 at a time, posting the json response into a VBA scripting dictionary, from which any of the 50 or so fields will be available via a simple UDF. Should have a file available tomorrow.

[JMN2]

Any hope for us using the simple yahoo price scrape? Thanks.

http://lemonfoolfinancialsoftware.weebl ... crape.html

[kiloran]

Any hope for us using the simple yahoo price scrape? Thanks.

http://lemonfoolfinancialsoftware.weebl ... crape.html

It won't work just now, but we'll find a suitable fix

--kiloran

[GN100]

On the original anc1 sheet I used to link through to the last price column. I did this because there were occasions when this was the only price displayed, the bid and offer being blank. However at the moment any price choice would be acceptable. I too feel like a fish out of water now.

[Raptor]

Kiloran.

When you get this fixed, any chance of adding the fix here and a quick "how to" so we can update old spreadsheet...

Raptor...

[Breelander]

...feel like a fish out of water now.

For now, my workaround is to manually enter the prices. As I have a virtual portfolio on DigitalLook mine can easily be found on a single page.

This won't automatically update the 'FTSE-HYP Tracking' sheet though, but that too can be manually updated.

[mike]

I too use anc's LivePrice under OpenOffice, and found the Yahoo feed of the bid & offer price too unreliable. So much so that I hid the columns and ignored them.

Thanks to all for your help, really is much appreciated.

Mike

[kiloran]

Kiloran.

When you get this fixed, any chance of adding the fix here and a quick "how to" so we can update old spreadsheet...

Raptor...

We'll try to, Raptor, depends on the complexity of the fix. Describing the fix might be more complicated than having to rebuild your HYPTUSS. We'll do all we can.

--kiloran

[Raptor]

Kiloran.

When you get this fixed, any chance of adding the fix here and a quick "how to" so we can update old spreadsheet...

Raptor...

We'll try to, Raptor, depends on the complexity of the fix. Describing the fix might be more complicated than having to rebuild your HYPTUSS. We'll do all we can.

--kiloran

I have been promising to upgrade my spreadsheet to the lastest for a few years now so maybe it is time to.

I do use latest one for my combined portfolio and have upgaded my daughter portfolio but neither of them have the complexity of my changes to my HYP one.

Raptor

[PeterGray]

On the downside, I will need to go back to manually updating my stock prices. On the upside, perhaps I will look at my portfolio less frequently which can only be a good thing!

Same problem here with my excel macros, based originally on the simple yahoo price scrape - or one of it's ancestors. I had at some point modified it to use google and just changed it back again to Yahoo in the last month or so!

I think I'll be manually updating for a while - until either someone here works some magic or I find the time to start scraping price from elsewhere.

Peter

[marben100]

Some more positive news - the portfolio server feed from yahoo still seems to be available, and spits out json dictionaries for lists of tickers

for example:

https://query1.finance.yahoo.com/v7/fin ... D.L,BARC.L

Its pretty versatile but will need some work to make the change. I don't know how long server access will last, but I'm going to build a new pricing module for excel and will make it available here when I'm happy with it

I was anc1 on TMF but I'm eventide on TLF btw

Hi eventide/anc1,

Just wanted to say a big thank you. I have been using your code pretty much ever since 2005 and it's become an integral part of my investment process, so I was pretty devastated when it stopped working yesterday. I use well over 100 different stock prices in a large number of spreadsheets performing various analytical functions (so utterly impractical to update stock prices manually). Luckily they all link back to a master spreadsheet that maintains the stock prices. Was about to start the painful task of looking to modify the code to use the portfolio server feed instead, but see that you are already working on it. Would really appreciate it if you would advise when you're happy with the new module and can make it available.

As an alternative I have considered getting the stock prices in a Google sheet and using Excel's Get From Web feature to extract the data from there - but to do that I observe that I'd have to change the ticker symbols for all the AIM stocks.

Many thanks,
Mark B

[eventide]

OK here we go. First crack.

this is BETA, feedback will be useful to see if it works for a few people. I won't leave it up for long
It is saved as a 1997-2003 file for max compatibility, but built in Office 365 so not sure how things will go

If it works properly I can prep a version with full code visibility so users can muck around with it.

https://www.dropbox.com/s/k4sppbgqyk2bz ... A.xls?dl=0

[marben100]

OK here we go. First crack.

this is BETA, feedback will be useful to see if it works for a few people. I won't leave it up for long
It is saved as a 1997-2003 file for max compatibility, but built in Office 365 so not sure how things will go

If it works properly I can prep a version with full code visibility so users can muck around with it.

https://www.dropbox.com/s/k4sppbgqyk2bz ... A.xls?dl=0

Have downloaded and made a start, putting in the tickers I want: so far so good!

Great work eventide! You're a lifesaver, I was tearing my hair out trying to find a workaround.

Mark B

PS I'm using Excel 2010

[kiloran]

OK here we go. First crack.

this is BETA, feedback will be useful to see if it works for a few people. I won't leave it up for long
It is saved as a 1997-2003 file for max compatibility, but built in Office 365 so not sure how things will go

If it works properly I can prep a version with full code visibility so users can muck around with it.

https://www.dropbox.com/s/k4sppbgqyk2bz ... A.xls?dl=0

We have a genius in our midst!
Excellent. Works for me, eventide, on Windows7 and Excel 2010.
I modified a few of the tickers to use some odd ones, such as VWRL and it worked fine (except for the pounds/pence issue with VWRL)

Many thanks.

I'll continue testing and also look at an OpenOffice equivalent.

--kiloran

[Itsallaguess]

OK here we go. First crack.

this is BETA, feedback will be useful to see if it works for a few people. I won't leave it up for long

It is saved as a 1997-2003 file for max compatibility, but built in Office 365 so not sure how things will go

Great news, and fantastic to hear that it's running OK for some people.

I've just tried it on Windows Excel 2002 SP3, and it comes up with an error on opening of 'Compile Error in hidden module: Utilities', and the same when I try to refresh any prices.

Not sure if you can achieve compatibility with such older versions of Excel, but just mentioning this in case there's a chance.

Cheers,

Itsallaguess

[spiderbill]

OK here we go. First crack.

this is BETA, feedback will be useful to see if it works for a few people.

Just tried it. Using Excel 2007 on Win 10. When I enable the macros it immediately gives me :

"Compile error in hidden modue: Utilities"

and won't do anything after that without the same message.

Hope that helps

Spiderbill

[marben100]

Hi eventide,

One query: in some of my spreadsheets I look at price changes from the previous day. The old GETDATA function had an argument value that returned the previous day's closing price. Is that available in the new version of the module and, if so, what is the magic value I need to supply to the second argument?

Thanks,
Mark B

[eventide]

The compile error may be due to a reference to Internet controls - I check if the Internet is connected before polling yahoo, so I will remove that test and resubmit.

The close price is already here - just change a column header to "close" or "prvcls" or create a new column with that header and it will pick up the close. Same with change. See the info to the right hand side of the spreadsheet