Updated Excel HYPTUSS available for test

[Nocton]

TY, kiloran, for the new price-scraping code from shareprices.com. It is nicely done and was easy for me to understand and incorporate into my own spreadsheet.
My portfolio consists of three parts: ITs, AIM shares and ETFs. It was easy to switch the HTML table to the AIM shares index, but there is no such option for ETFs. I thought I might be able to solve the problem by putting the ETFs in a Watchlist and pointing to that page, but the table was not picked up.
Have you any ideas?

[kiloran]

TY, kiloran, for the new price-scraping code from shareprices.com. It is nicely done and was easy for me to understand and incorporate into my own spreadsheet.
My portfolio consists of three parts: ITs, AIM shares and ETFs. It was easy to switch the HTML table to the AIM shares index, but there is no such option for ETFs. I thought I might be able to solve the problem by putting the ETFs in a Watchlist and pointing to that page, but the table was not picked up.
Have you any ideas?

I don't think the SharePrices website has any tables for ETFs, but you can get the data on a share=by-share basis from https://shareprices.com/lse/vwrl/
This is what HYPTUSS does to fill in the gaps for shares/ETFs not in the all-share index

--kiloran

[Nocton]

TY, kiloran, for the new price-scraping code from shareprices.com. It is nicely done and was easy for me to understand and incorporate into my own spreadsheet.
My portfolio consists of three parts: ITs, AIM shares and ETFs. It was easy to switch the HTML table to the AIM shares index, but there is no such option for ETFs. I thought I might be able to solve the problem by putting the ETFs in a Watchlist and pointing to that page, but the table was not picked up.
Have you any ideas?

I don't think the SharePrices website has any tables for ETFs, but you can get the data on a share=by-share basis from https://shareprices.com/lse/vwrl/
This is what HYPTUSS does to fill in the gaps for shares/ETFs not in the all-share index

--kiloran

TY, kiloran. In fact it seems simplest to use the getSharePrice_1 function for all my portfolio as it avoids the need for getting the All share or AIM share tables.
Again, many thanks for your great service to us all.

[csearle]

In fact it seems simplest to use the getSharePrice_1 function for all my portfolio as it avoids the need for getting the All share or AIM share tables.

Thank you ever so much. Following this useful observation I have incorporated getSharePrice_1 in my rather quirky spreadsheet (with a rather clunky augmentation to get ^FTSE and ^FTAS too).

I use HYPTUSS for my share top-up process, but not for my daily (hourly ) HYP check.

I'd like to add my thanks to Kiloran (and of course Itsallaguess) for their continued efforts.

Chris

[funduffer]

In fact it seems simplest to use the getSharePrice_1 function for all my portfolio as it avoids the need for getting the All share or AIM share tables.

Thank you ever so much. Following this useful observation I have incorporated getSharePrice_1 in my rather quirky spreadsheet (with a rather clunky augmentation to get ^FTSE and ^FTAS too).

I use HYPTUSS for my share top-up process, but not for my daily (hourly ) HYP check.

I'd like to add my thanks to Kiloran (and of course Itsallaguess) for their continued efforts.

Chris

Quick question.

Is getSharePrice_1 an Excel addin like tlfaddin (which codes the 'getdata' function)?

For those of us not VBA literate, is it a viable replacement for tlfaddin?

Thanks for any advice that can be offered.

(I am currently entering share prices manually, and considering subscribing to Office 365 so I can get access to STOCKS, but would prefer not to!)

FD

[csearle]

Thank you ever so much. Following this useful observation I have incorporated getSharePrice_1 in my rather quirky spreadsheet (with a rather clunky augmentation to get ^FTSE and ^FTAS too).

I use HYPTUSS for my share top-up process, but not for my daily (hourly ) HYP check.

I'd like to add my thanks to Kiloran (and of course Itsallaguess) for their continued efforts.

Chris

Quick question.

Is getSharePrice_1 an Excel addin like tlfaddin (which codes the 'getdata' function)?

For those of us not VBA literate, is it a viable replacement for tlfaddin?

Thanks for any advice that can be offered.

(I am currently entering share prices manually, and considering subscribing to Office 365 so I can get access to STOCKS, but would prefer not to!)

FD

getSharePrice_1 is a VBA function that can be called (for example when a macro is run). It takes an EPIC as a parameter and returns the share price (and currency unit). Not familiar with tlfaddin. C.

[kiloran]

Thank you ever so much. Following this useful observation I have incorporated getSharePrice_1 in my rather quirky spreadsheet (with a rather clunky augmentation to get ^FTSE and ^FTAS too).

I use HYPTUSS for my share top-up process, but not for my daily (hourly ) HYP check.

I'd like to add my thanks to Kiloran (and of course Itsallaguess) for their continued efforts.

Chris

Quick question.

Is getSharePrice_1 an Excel addin like tlfaddin (which codes the 'getdata' function)?

For those of us not VBA literate, is it a viable replacement for tlfaddin?

Thanks for any advice that can be offered.

(I am currently entering share prices manually, and considering subscribing to Office 365 so I can get access to STOCKS, but would prefer not to!)

FD

No, getSharePrice_1 is not an add-in. A month or so ago, I was pondering trying to get EvenTide's tlfaddin working again, but that's now way down my to-do list since Yahoo pulled the rug from under us.

--kiloran

[Nocton]

As you said, csearle, that you are not "VBA literate" I now show you what to do in order to use kiloran's function.

You first need to have a worksheet where you put your Epic codes. In my example the Epic codes are in column A/1 and start at row 4 and can go up to row 40.
You then need to open the VBA editor - as I am using an old version of Excel - before the ribbon menus - I can't tell you exactly where to find it, but a quick web search for your Excel version will show you what to do.
Then in the editor, navigate to your new worksheet and right- click to select 'View code'
Then paste the code below into the area:
This a sub-routine - it begins with Sub and ends with End Sub. I have put some comments in to help you understand it - they are anything after ' and will show in green in the VBA editor.
Looking at the code:
1. There is a MsgBox command to give you the option of updating or not and another to tell you that all has been updated.
2. My worksheet is called "YahooDownload". You will need to replace it with the name of your worksheet.
3. EpicCol is the column in which the Epic codes are to be found, currently set to 1; PriceCol is where the prices will be put, currently set to 3.
4. The variable 'Old' picks up the old prices and then at the end we can compute the change in price and put it in the column after PriceCol.
5. Of course you also need to paste in kiloran's price-getting function getSharePrice_1 which the sub-routine calls.

Finally, to make the sub easy to use, you need to call it from a command button on your worksheet (or wherever you wish to put it). If you are not sure how to do this, please ask. Without the command button, you can start the sub by putting the cursor somewhere within the sub and clicking on the Run option from the VBA editor menu menu.

I hope this is helpful for you.

Code: Select all

Sub GetPrices1()

Dim I, Response, EpicCol, PriceCol As Integer
Dim Old, price, EpicSymbol As String
Dim priceData() As Variant
               
Response = MsgBox("Update Prices?", vbYesNo, "Update from Yahoo")
If Response = vbNo Then
    MsgBox "Prices NOT updated"
    Exit Sub
End If

EpicCol = 1
PriceCol = 3

For I = 4 To 40
Sheets("YahooDownload").Select
    EpicSymbol = Cells(I, EpicCol).Value
    Old = Cells(I, PriceCol).Value
    If EpicSymbol <> "" Then
        priceData = getSharePrice_1(EpicSymbol)  'call kiloran's price-getting function
        price = priceData(1)
      If price = "" Then price = "-1"  'indicate with - if no price could be obtained/invalid epic
        Cells(I, PriceCol).Value = Left(price, Len(price) - 1)  'strip p from price
        If IsNumeric(Old) Then Cells(I, PriceCol + 1).Value = (Cells(I, PriceCol).Value - Old) 'compute price change
      End If
Next 'I

Columns(PriceCol).AutoFit
Range("A1").Select
MsgBox "OK, Prices updated"

End Sub

[kiloran]

Thanks to everyone for the feedback. I've published an updated Excel version v11-85-dev11 at: https://lemonfoolfinancialsoftware.weeb ... _page.html

The changes:

1. Fixed ZCASH
2. Fixed the Single Chart and All Charts features. LiveCharts is no longer available as a chart source.... Yet another website which has changed to interactive charts with embedded javascript which is of no use for HYPTUSS. The only chart source now available is Trustnet
3. Tracking sheet problems have been fixed. And the benchmark indices are now fixed at FTSE100 and FTSE all-share
4. Sector weighting chart now works (not sure why it didn't)
5. various currency conversion issues have been fixed
6. Multiple updates to the Company Data Sheet
7. Changed the "Get Yahoo Prices" button to "Get Prices"

Hopefully that has fixed most bugs and we are nearer to the final release.

I'll now get working on bug-fixes for the LibreOffice version

--kiloran

[uspaul666]

> Multiple updates to the Company Data Sheet
You’ve not uploaded the updated company_data_sheet.txt.

[kiloran]

> Multiple updates to the Company Data Sheet
You’ve not uploaded the updated company_data_sheet.txt.

Not when I posted, but it was published about 1 hr later
Unless I screwed up, of course!

--kiloran

[uspaul666]

Oh, I think maybe I was confused. I expected to see it on the "HYP Test Page". Carry on, nothing to see here...

[uspaul666]

I can't think it affects many round here but the company_data_sheet.txt file contains two entries for the epic value 'NCYF' at lines 143 & 144: 'CQS New City High Yield Fund Limited' cf 'CQS New City High Yield Fund Ltd'

[kiloran]

I can't think it affects many round here but the company_data_sheet.txt file contains two entries for the epic value 'NCYF' at lines 143 & 144: 'CQS New City High Yield Fund Limited' cf 'CQS New City High Yield Fund Ltd'

Thanks, I've updated my master copy and will include it in the next release

--kiloran

[csearle]

As you said, csearle, that you are not "VBA literate" I now show you what to do in order to use kiloran's function.

Are you absolutely sure I said that¹?

Nevertheless, your post is without doubt useful for many.

Thanks,
Chris
¹ I've been programming in VBA, on and off, since its inception.

[Bena48]

The previous version often seemed to struggle to find prices and/or dividend yield for NWG, SONG and of course BHP.


Thanks for your continuing work.

Do you have plans for the Stephen sheet?

[kiloran]

The previous version often seemed to struggle to find prices and/or dividend yield for NWG, SONG and of course BHP.

Thanks for your continuing work.

Do you have plans for the Stephen sheet?

I assume you are implying that NWG, SONG and BHP are now OK??

Stephen sheet????? What's that?

--kiloran

[Bena48]

The previous version often seemed to struggle to find prices and/or dividend yield for NWG, SONG and of course BHP.

Thanks for your continuing work.

Do you have plans for the Stephen sheet?

I assume you are implying that NWG, SONG and BHP are now OK??

Stephen sheet????? What's that?

--kiloran

No I have not tested the new sheet but merely observing that these shares did not always register on the old ones.

Stepone (damn spellcheck)

[kiloran]

I assume you are implying that NWG, SONG and BHP are now OK??

Stephen sheet????? What's that?

--kiloran

No I have not tested the new sheet but merely observing that these shares did not always register on the old ones.

Stepone (damn spellcheck)

The price retrieval seems to be reliable for me. SONG is not in the Company Data Sheet..... did you add it to your copy?
I'll add it in the next release

Stephen/StepOne. Doh, obvious now you mention it. Yes, I'll be fixing that, but the current focus is on HYPTUSS

--kiloran

[jeff50]

Hi Kiloran,
Thank you once again for your sterling work and support to the Hyptuss community in sustaining and maintaining a valuable asset to many.
I have downloaded the updated v11-85 dev11 version and have a query.
I HOLD Melrose and from a demerger now hold shares in Dowlais (DWL).
When I run the the price update it fails to give a price for Dowlais. It did give a price under the Yahoo price update when that was operating under the V84 version.
I have rechecked the Company data entry (/equity/Dowlais_Group Dowlais Group DWL Engineering DWL) and that appears to be ok. Any thoughts?
Regards
Jeff