HYPTUS v11.31 Run-time error 5

[eventide]

Here is an amended version, no internet check, and a previous close column...

If this doesn't work in Excel 2002 SP3 I'm not sure I can get you there sadly.

https://www.dropbox.com/s/altci9amqewte ... 2.xls?dl=0

[Itsallaguess]

Here is an amended version, no internet check, and a previous close column...

If this doesn't work in Excel 2002 SP3 I'm not sure I can get you there sadly.

https://www.dropbox.com/s/altci9amqewte ... 2.xls?dl=0

Works fine in Excel 2002 SP3 now, which is great news, thanks.

I should add that I only use this version, along with the XP software that runs it, as part of my extensive software back-testing kit.... :O)

Cheers,

Itsallaguess

[kiloran]

Here is an amended version, no internet check, and a previous close column...

If this doesn't work in Excel 2002 SP3 I'm not sure I can get you there sadly.

https://www.dropbox.com/s/altci9amqewte ... 2.xls?dl=0

Still works fine for me, eventide. Win7/excel2010

One extra that you might consider sometime is the ability to report currency. Some examples:

VOD is GBp
VWRL is GBP
TJI is USD
SMTG is EUR

--kiloran

[spiderbill]

Here is an amended version, no internet check, and a previous close column...

Works in Excel 2007, excellent!

[kiloran]

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

Thanks for that, 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).

--kiloran

I just tested the link and it happily handles over 1000 shares at once, so no problems from that point of view. I couldn't be bothered to test any further!
I've got the basis of a python script working so it looks like we are on the way.
I seem to remember that the old process returned N/A or somesuch if a symbol was not recognised, so VOD.L+A99.L+SSE.L returned something like 221.0,N/A,1350.0.
The new process just ignores invalid symbols, so returns 221.0,1350.0. Need to be careful with this

--kiloran

[mc2fool]

http://download.finance.yahoo.com/d/quotes.csv&s=BA.l&f=l1&e=.csv

Pasting that into a browser gives the same 'Sorry, Unable to process request at this time -- error 999.' as you report.

Now it says: "It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com."

Oh well, it was good while it lasted ... wonder if they'll also do the same with the JSON service sooner or later ...

[eventide]

I just tested the link and it happily handles over 1000 shares at once, so no problems from that point of view. I couldn't be bothered to test any further!
I've got the basis of a python script working so it looks like we are on the way.
I seem to remember that the old process returned N/A or somesuch if a symbol was not recognised, so VOD.L+A99.L+SSE.L returned something like 221.0,N/A,1350.0.
The new process just ignores invalid symbols, so returns 221.0,1350.0. Need to be careful with this

--kiloran

Yep, that portfolio link seems unrestricted in how many tickers you can call. And the code I created is immune to a malformed ticker so it would just continue to return ::queued:: in the event the ticker is not recognised by the yahoo server.

It seems you are on your way with the new server address and since you can deal with the json in python a lot more easily, I'll remove the test excel spreadsheet and let you get on with it. I'm not a hyp afficionado, just like fast and easy stock prices into excel to monitor any portfolio.

cheers

[kiloran]

I just tested the link and it happily handles over 1000 shares at once, so no problems from that point of view. I couldn't be bothered to test any further!
I've got the basis of a python script working so it looks like we are on the way.
I seem to remember that the old process returned N/A or somesuch if a symbol was not recognised, so VOD.L+A99.L+SSE.L returned something like 221.0,N/A,1350.0.
The new process just ignores invalid symbols, so returns 221.0,1350.0. Need to be careful with this

--kiloran

Yep, that portfolio link seems unrestricted in how many tickers you can call. And the code I created is immune to a malformed ticker so it would just continue to return ::queued:: in the event the ticker is not recognised by the yahoo server.

It seems you are on your way with the new server address and since you can deal with the json in python a lot more easily, I'll remove the test excel spreadsheet and let you get on with it. I'm not a hyp afficionado, just like fast and easy stock prices into excel to monitor any portfolio.

cheers

eventide, the code on your spreadsheet is protected. Could you enable access to the VBA code, it would perhaps give itsallaguess and me a flying start in fixing the Excel version of the HYPTUSS. The python code is only of use in the OpenOffice version.

Many thanks for your efforts, it's much appreciated

--kiloran

[PeterGray]

eventide/kiloran

I'd be very interested in seeing the VBA code that appears to work for yahoo - your's is no longer available on dropbox, eventide.

I need to modify my own excel ss to work, and I'd rather not have to start from scratch!

Peter

[eventide]

eventide/kiloran

I'd be very interested in seeing the VBA code that appears to work for yahoo - your's is no longer available on dropbox, eventide.

I need to modify my own excel ss to work, and I'd rather not have to start from scratch!

Peter

Now my quick hack seems to work for most Excel configurations I have pulled the link and will make the code a bit more robust before putting it up in new post somewhere more visible on TLF in a few days.

If you are modding your own vba, its pretty easy to poll the other server mentioned on this thread and parse the returned json string to pack an array or dictionary.

[peterh]

eventide/kiloran

I'd be very interested in seeing the VBA code that appears to work for yahoo - your's is no longer available on dropbox, eventide.

I need to modify my own excel ss to work, and I'd rather not have to start from scratch!

Peter

Now my quick hack seems to work for most Excel configurations I have pulled the link and will make the code a bit more robust before putting it up in new post somewhere more visible on TLF in a few days.

If you are modding your own vba, its pretty easy to poll the other server mentioned on this thread and parse the returned json string to pack an array or dictionary.

Can I add my belated thanks and appreciation for LivePrice (and your current efforts) as well? I checked my LivePrice file and it has a create date in 2003 and I've been using for most of the intervening years!

If you were to publish an 'unlocked' version, it would be much appreciated - I did a fair bit of VBA development a few years ago, but not using APIs, so I'd be keen to see how it works in a practical situation.

[Itsallaguess]

Just to let you all know that we're making some good progress on the price-scraping issues. We need to carry out some further tests, but it's looking better than it did a few days ago...

Cheers,

Itsallaguess

[kiloran]

The software gnomes have been beavering away and made some progress.

I've updated the OpenOfficeLibreOffice version of the "Simple Yahoo Price Scrape 1-2.ods" at: http://lemonfoolfinancialsoftware.weebl ... crape.html

This is essentially a test vehicle for the Python code I plan on migrating to the OpenOffice/LibreOffice version of the HYPTUSS. If you want to update your HYPTUSS in the meantime, you can copy the list of tickers to the simple price scrape, update the prices, then copy the prices to HYPTUSS. Crude, but I don't want to rush out a new version of HYPTUSS and then spread confusion by issuing multiple bug fixes. I'd like to get it right first time, even if that means a few days delay.

If you don't have LibreOffice and don't want to install it, you could use the portable version which puts the whole application into a single folder and makes no changes to the registry, etc. You can then delete the folder when you no longer have need of it.
https://portableapps.com/apps/office/li ... e_portable
You will need to enable macros (TOOLS/OPTIONS/LIBREOFFICE/SECURITY/MACRO SECURITY/MEDIUM)

Itsallaguess has already made progress with an updated excel HYPTUSS. It's essentially working though I have just found a bug (you just can't get quality staff these days ), so hopefully we'll soon have the HYPTUSS back on the rails.

Many many thanks to eventide/anc1 for pointing us in the right direction

--kiloran

[PeterGray]

Great news - thanks!

[marben100]

Here is an amended version, no internet check, and a previous close column...

If this doesn't work in Excel 2002 SP3 I'm not sure I can get you there sadly.

https://www.dropbox.com/s/altci9amqewte ... 2.xls?dl=0

Hi eventide,

Unfortunately I can't download this version - Dropbox is saying file not found or removed.

In the older version, which I'm using, I have found a minor problem: prvcls returns the same value as last but previousclose works fine.

Best,
Mark

[Itsallaguess]

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

I meant to come back to this earlier comment Raptor, just to remind everyone that I created an online video tutorial clearly showing the HYPTUSS transfer process for when these issues mean we've got to migrate to the next working version -

https://vimeo.com/232215342

It really is a fairly simple process for anyone just using the vanilla tool, so hopefully the above helps to allay any fears.

For anyone who has created any 'bolt-on' sheets or additional functionality, there may be some extra work to do, but that's a situation that's unavoidable for these types of issues. Hopefully the people carrying out these types of modifications are able to quickly replicate them in the new versions.

Of course, if anyone has any questions regarding the above video process, then please just ask and we'll try to help out.

We're happy to report that the HYPTUSS modifications and testing are going well, so please be confident that we can get you all back up and running fairly soon.

Cheers,

Itsallaguess

[Raptor]

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

I meant to come back to this earlier comment Raptor, just to remind everyone that I created an online video tutorial clearly showing the HYPTUSS transfer process for when these issues mean we've got to migrate to the next working version -

https://vimeo.com/232215342

It really is a fairly simple process for anyone just using the vanilla tool, so hopefully the above helps to allay any fears.

For anyone who has created any 'bolt-on' sheets or additional functionality, there may be some extra work to do, but that's a situation that's unavoidable for these types of issues. Hopefully the people carrying out these types of modifications are able to quickly replicate them in the new versions.

Of course, if anyone has any questions regarding the above video process, then please just ask and we'll try to help out.

We're happy to report that the HYPTUSS modifications and testing are going well, so please be confident that we can get you all back up and running fairly soon.

Cheers,

Itsallaguess

Unfortunately I have made macro, VBA, formula etc changes. As an ex-programmer you would have thought I would have learned and recorded my work, but no............. The other portfolios are usually vanilla so upgrading is easy and I use them to "test" your updates. Gives me a chance to re-visit why I made the changes/additions in the first place and see if they are now needed.

Thanks for all your efforts, much appreciated.

Raptor.

[vrdiver]

Unfortunately I have made macro, VBA, formula etc changes.

Raptor.

If you do decide to upgrade to the new sheet, one option might be to reproduce your portfolio in the new vanilla version, and then just link the values from the new version into your customised sheet.

I have my own s/sheet (which doesn't include the HYPTUSS code) but gets it's data from a vanilla version of HYPTUSS, e.g.

=VLOOKUP(A20,'[hyp_top-up_spreadsheet_-_v11.30.xlsm]High Yield Portfolio'!$C$6:$E$1001,3,) searches for the price of the ticker in A20 of my sheet in a copy of HYPTUSS that sits in the same folder.

Updates to my custom sheet, when a new HYPTUSS is issued, are simple: just a global search and replace for the old sheet name to be replaced by the new sheet name.

In your case, you might want to remove the macro buttons so as not to run them by mistake which would delete your changes!

VRD

[Raptor]

Unfortunately I have made macro, VBA, formula etc changes.

Raptor.

If you do decide to upgrade to the new sheet, one option might be to reproduce your portfolio in the new vanilla version, and then just link the values from the new version into your customised sheet.

I have my own s/sheet (which doesn't include the HYPTUSS code) but gets it's data from a vanilla version of HYPTUSS, e.g.

=VLOOKUP(A20,'[hyp_top-up_spreadsheet_-_v11.30.xlsm]High Yield Portfolio'!$C$6:$E$1001,3,) searches for the price of the ticker in A20 of my sheet in a copy of HYPTUSS that sits in the same folder.

Updates to my custom sheet, when a new HYPTUSS is issued, are simple: just a global search and replace for the old sheet name to be replaced by the new sheet name.

In your case, you might want to remove the macro buttons so as not to run them by mistake which would delete your changes!

VRD

That was so simple. Now why did I not do that before as I have 4 seperate spreadsheets, a combined for all my investments, one just for IT's, one for HYP and another to track which account the dividends actually go into. With this I can reduce this by using the latest and gratest HYPTUSS for the combined, move the dividend one to the HYP ( do not care if it contains everything). STOP, I have now just made myself loads of extra work.....

Raptor (job for December so as not to look at my portfolio, except to test this "fix" of course).

[Itsallaguess]

We're pleased to report that we've got a couple of Beta versions of the new HYPTUSS tool for people to have a play with.

We'd normally do some more testing to be honest, and then try to get some finalised versions out, but Kiloran and I have some time-pressures that mean this is a little more difficult at the moment, and we're hoping that some community-Beta-testing might be a good way to get these releases out as quick as we can, given the impact the recent Yahoo issue has caused.

If anyone would like to take a look at the Beta versions, then they can be downloaded from this test-page on the Weebly site -

http://lemonfoolfinancialsoftware.weebl ... _page.html

As well as getting the price-feeds working again, we've also implemented an improvement in the way the tool handles some share prices that are returned from Yahoo in GBP (pounds) terms, rather than the normally-expected GBp (pence) terms, and these shares are now converted into pence automatically.

Any feedback would be very helpful from anyone wishing to help with these tests, but can I please ask that if any issues are found then specifying which version you're looking at would be most helpful (the Excel version or the Libre/Open Office version), as well as a detailed explanation of any problems found, and how we can replicate the issue ourselves to try to investigate.

Thanks again to anc1/eventide for the helpful pointers towards the server-feed information.

Given the impact of this issue, and the fact that we may not be able to rely on the feed we've got working currently to last the same length of time as the previous one, we are going to look into a longer-term solution similar to the dividend-scrapes, where a parameter option can be used to toggle different price-source information, but for now we're back up and running with a single-source feed only, and that improvement is planned to be implemented in a later release.

Again, if anyone is struggling to move from an earlier version to a newer version of the tools, then this video will hopefully show how simple the process is -

https://vimeo.com/232215342

If there's any questions, comments, or feedback from the above Beta versions, then please just let us know.

Cheers,

Itsallaguess