Got a credit card? use our Credit Card & Finance Calculators
Thanks to gvonge,Shelford,GrahamPlatt,gpadsa,Steffers0, for Donating to support the site
Updated Excel HYPTUSS available for test
-
- Lemon Slice
- Posts: 497
- Joined: November 6th, 2016, 11:25 am
- Has thanked: 135 times
- Been thanked: 140 times
Re: Updated Excel HYPTUSS available for test
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?
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?
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
Nocton wrote: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
-
- Lemon Slice
- Posts: 497
- Joined: November 6th, 2016, 11:25 am
- Has thanked: 135 times
- Been thanked: 140 times
Re: Updated Excel HYPTUSS available for test
kiloran wrote:Nocton wrote: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.
-
- Lemon Quarter
- Posts: 4855
- Joined: November 4th, 2016, 2:24 pm
- Has thanked: 4887 times
- Been thanked: 2131 times
Re: Updated Excel HYPTUSS available for test
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).Nocton wrote: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.
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
-
- Lemon Quarter
- Posts: 1343
- Joined: November 4th, 2016, 12:11 pm
- Has thanked: 124 times
- Been thanked: 852 times
Re: Updated Excel HYPTUSS available for test
csearle wrote: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).Nocton wrote: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.
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
-
- Lemon Quarter
- Posts: 4855
- Joined: November 4th, 2016, 2:24 pm
- Has thanked: 4887 times
- Been thanked: 2131 times
Re: Updated Excel HYPTUSS available for test
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.funduffer wrote:csearle wrote: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
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
funduffer wrote:csearle wrote: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
-
- Lemon Slice
- Posts: 497
- Joined: November 6th, 2016, 11:25 am
- Has thanked: 135 times
- Been thanked: 140 times
Re: Updated Excel HYPTUSS available for test
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.
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
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
Thanks to everyone for the feedback. I've published an updated Excel version v11-85-dev11 at: https://lemonfoolfinancialsoftware.weeb ... _page.html
The changes:
I'll now get working on bug-fixes for the LibreOffice version
--kiloran
The changes:
- Fixed ZCASH
- 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
- Tracking sheet problems have been fixed. And the benchmark indices are now fixed at FTSE100 and FTSE all-share
- Sector weighting chart now works (not sure why it didn't)
- various currency conversion issues have been fixed
- Multiple updates to the Company Data Sheet
- Changed the "Get Yahoo Prices" button to "Get Prices"
I'll now get working on bug-fixes for the LibreOffice version
--kiloran
-
- 2 Lemon pips
- Posts: 233
- Joined: November 4th, 2016, 6:35 am
- Has thanked: 196 times
- Been thanked: 112 times
Re: Updated Excel HYPTUSS available for test
> Multiple updates to the Company Data Sheet
You’ve not uploaded the updated company_data_sheet.txt.
You’ve not uploaded the updated company_data_sheet.txt.
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
uspaul666 wrote:> 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
-
- 2 Lemon pips
- Posts: 233
- Joined: November 4th, 2016, 6:35 am
- Has thanked: 196 times
- Been thanked: 112 times
Re: Updated Excel HYPTUSS available for test
Oh, I think maybe I was confused. I expected to see it on the "HYP Test Page". Carry on, nothing to see here...
-
- 2 Lemon pips
- Posts: 233
- Joined: November 4th, 2016, 6:35 am
- Has thanked: 196 times
- Been thanked: 112 times
Re: Updated Excel HYPTUSS available for test
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'
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
uspaul666 wrote: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
-
- Lemon Quarter
- Posts: 4855
- Joined: November 4th, 2016, 2:24 pm
- Has thanked: 4887 times
- Been thanked: 2131 times
Re: Updated Excel HYPTUSS available for test
Are you absolutely sure I said that¹?Nocton wrote:As you said, csearle, that you are not "VBA literate" I now show you what to do in order to use kiloran's function.
Nevertheless, your post is without doubt useful for many.
Thanks,
Chris
¹ I've been programming in VBA, on and off, since its inception.
Re: Updated Excel HYPTUSS available for test
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?
Thanks for your continuing work.
Do you have plans for the Stephen sheet?
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
Bena48 wrote: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
Re: Updated Excel HYPTUSS available for test
kiloran wrote:Bena48 wrote: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)
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Updated Excel HYPTUSS available for test
Bena48 wrote:kiloran wrote: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
Re: Updated Excel HYPTUSS available for test
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
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
Return to “Financial Software - Discussion”
Who is online
Users browsing this forum: No registered users and 5 guests