import latest fund (OEIC) prices in excel
-
- Posts: 6
- Joined: August 27th, 2020, 10:17 pm
import latest fund (OEIC) prices in excel
Hiya,
I know this could be repeat topic, but being not clear so thought of asking again.
I have invest in two funds Vanguard global equity fund and Rathbones Global Opportunities fund through regular investing mode (i.e. drip feeding). Want to track it's performance on regular basis.
Can somebody tell me how can I import daily closing price (only need last day price not historic ones).
I don't know much about screenscraping nor vba coding, but can try if somebody can help.
Thanks
Anks
I know this could be repeat topic, but being not clear so thought of asking again.
I have invest in two funds Vanguard global equity fund and Rathbones Global Opportunities fund through regular investing mode (i.e. drip feeding). Want to track it's performance on regular basis.
Can somebody tell me how can I import daily closing price (only need last day price not historic ones).
I don't know much about screenscraping nor vba coding, but can try if somebody can help.
Thanks
Anks
-
- Lemon Half
- Posts: 7675
- Joined: November 4th, 2016, 11:20 am
Re: import latest fund (OEIC) prices in excel
Set up a portfolio on Trustnet with your two funds. https://www2.trustnet.com/Tools/Portfol ... oHome.aspx
The prices are usually updated by about 5pm, but it depends on whether the fund managers have updated their website.
You can update your number of units and the cost as required, or you can just copy the prices into a spreadsheet of your own.
TJH
The prices are usually updated by about 5pm, but it depends on whether the fund managers have updated their website.
You can update your number of units and the cost as required, or you can just copy the prices into a spreadsheet of your own.
TJH
-
- Posts: 6
- Joined: August 27th, 2020, 10:17 pm
Re: import latest fund (OEIC) prices in excel
Thanks TJH
I have set up one at trustnet earlier.
However I am maintaining an excel sheet - was thinking if I could just import the latest price of a fund. I know manually entering all regular investments on trustnet would be little cumbersome as my investment will grow and entering it manually would also be timeconsuming, hence was thinking how could I just get the fund closing price in my excel.
If it's there any code or google finance sheet trick, I am happy to try that out. Just need you bit guidance
Thanks
Anks
I have set up one at trustnet earlier.
However I am maintaining an excel sheet - was thinking if I could just import the latest price of a fund. I know manually entering all regular investments on trustnet would be little cumbersome as my investment will grow and entering it manually would also be timeconsuming, hence was thinking how could I just get the fund closing price in my excel.
If it's there any code or google finance sheet trick, I am happy to try that out. Just need you bit guidance
Thanks
Anks
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: import latest fund (OEIC) prices in excel
This is what you want: http://lemonfoolfinancialsoftware.weebl ... crape.htmlamittal6 wrote:I don't know much about screenscraping nor vba coding, but can try if somebody can help.
You can nick the VBA code from that and incorporate it into your own spreadsheet. It gets the prices from Yahoo Finance, and for OEICs you have to have a search on there to get the (Yahoo specific) ticker to use. E.g. https://finance.yahoo.com/quote/0P0001FE43.L
But the first thing you want to do is to report your topic post and ask the gods to move this thread to the Financial Software board, which is where the folks that wrote that tend to hang out and the best place to ask for help on this sort of thing (click the ! at the top right of your initial post).
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: import latest fund (OEIC) prices in excel
Hi there,amittal6 wrote:
I know this could be repeat topic, but being not clear so thought of asking again.
I have invest in two funds Vanguard global equity fund and Rathbones Global Opportunities fund through regular investing mode (i.e. drip feeding). Want to track it's performance on regular basis.
Can somebody tell me how can I import daily closing price (only need last day price not historic ones).
I don't know much about screenscraping nor vba coding, but can try if somebody can help.
We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago, so I think that should be able to deliver your requirements.
Here's a snapshot of the functionality and demo funds included in the current version of the tool -
![Image](https://i.imgur.com/BszZDNq.png)
You can search for the required fund information using this link -
https://markets.ft.com/data/funds/uk
If that looks like it might deliver what you're looking for, then you can download the latest version of HYPTUSS using the link below. There's a version for Excel and also one for LibreOffice -
http://lemonfoolfinancialsoftware.weebl ... op-up.html
The stand-alone fund-price functionality is contained on a sheet called 'FT Funds', but if you give it a go and have any questions then just let me know.
If you or anyone else wants to incorporate this VBA process into your own financial spreadsheets, then the password for the VBA code access is 'pleaseletmein'
Cheers,
Itsallaguess
-
- Lemon Half
- Posts: 7039
- Joined: November 4th, 2016, 10:53 am
Re: import latest fund (OEIC) prices in excel
amittal6 wrote:Hiya,
I know this could be repeat topic, but being not clear so thought of asking again.
Thanks
Anks
Moderator Message:
Relocated per your request, regards, dspp
Relocated per your request, regards, dspp
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: import latest fund (OEIC) prices in excel
I've not looked at HYPTUSS for yonks (have my own spreadsheet hacked up over a decade or so), but that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism? Indeed, sticking a Yahoo fund code into the ticker column of the HYP sheet and clicking Get Yahoo Prices works fine (and, of course, does it all in a single HTTP get, rather than one per fund).Itsallaguess wrote:We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago
I must say, HYPTUSS has certainly grown since I last looked at it! If the OP wants an all singing all dancing solution it could well be best, but OTOH if they want something simple to grow themselves, methinks the simple Yahoo price scrape would be a better place to start.
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: import latest fund (OEIC) prices in excel
One of the benefits of using the FT site is that it provides an easy link to a summary page, such as https://markets.ft.com/data/funds/tears ... N45980:GBXmc2fool wrote:I've not looked at HYPTUSS for yonks (have my own spreadsheet hacked up over a decade or so), but that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism? Indeed, sticking a Yahoo fund code into the ticker column of the HYP sheet and clicking Get Yahoo Prices works fine (and, of course, does it all in a single HTTP get, rather than one per fund).Itsallaguess wrote:We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago
I must say, HYPTUSS has certainly grown since I last looked at it! If the OP wants an all singing all dancing solution it could well be best, but OTOH if they want something simple to grow themselves, methinks the simple Yahoo price scrape would be a better place to start.
Yahoo does not have this
--kiloran
-
- Posts: 6
- Joined: August 27th, 2020, 10:17 pm
Re: import latest fund (OEIC) prices in excel
mc2fool wrote:I've not looked at HYPTUSS for yonks (have my own spreadsheet hacked up over a decade or so), but that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism? Indeed, sticking a Yahoo fund code into the ticker column of the HYP sheet and clicking Get Yahoo Prices works fine (and, of course, does it all in a single HTTP get, rather than one per fund).Itsallaguess wrote:We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago
I must say, HYPTUSS has certainly grown since I last looked at it! If the OP wants an all singing all dancing solution it could well be best, but OTOH if they want something simple to grow themselves, methinks the simple Yahoo price scrape would be a better place to start.
I think with Yahoo, my view is that it is not reflecting the true NAV. For example, I was checking the NAV of Baillie Gifford Emerging markets Leading companies B Acc fund and it is showing me as of July 8th. Why on earth I wanted to get the NAV of historical date. I am looking for latest NAV (say of last closing date).
In terms of using HYPTUSS tool, am not very sure if I am capable enough to do it. Doing it from FT site would just be fine.
Thinking of how can I automate it. Have not yet figured out.
-
- Posts: 6
- Joined: August 27th, 2020, 10:17 pm
Re: import latest fund (OEIC) prices in excel
Exactly - utility of using it again and again is the key. Like I just said above in my response, I am not even clear whether yahoo is publishing the latest NAV even.
Kiloran - I'd rather would with your advice and appreciate your help!
Kiloran - I'd rather would with your advice and appreciate your help!
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: import latest fund (OEIC) prices in excel
Umm ... well I'm not sure how that answers my question as to why you decided to fetch prices from the FT as opposed to using the existing Yahoo mechanism.kiloran wrote:One of the benefits of using the FT site is that it provides an easy link to a summary page, such as https://markets.ft.com/data/funds/tears ... N45980:GBX
And I might also ask, why does the top fund in the downloaded HYPTUSS (Aegon International High Yield Bond A) have the Yahoo symbol against it?
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
That is a bit strange, considering if you go to the history page for it it has all the prices up to yesterday. But then Yahoo does often have its little quirks.amittal6 wrote:I think with Yahoo, my view is that it is not reflecting the true NAV. For example, I was checking the NAV of Baillie Gifford Emerging markets Leading companies B Acc fund and it is showing me as of July 8th.
![Cool 8-)](./images/smilies/icon_cool.gif)
Well, the easy (a relative term!) answer to that is you nick the VBA from HYPTUSS, and then incorporate it into your spreadsheet (modifying where necessary). That act itself is no more difficult than doing so from the simple yahoo scrape spreadsheet ... just there's a lot more not-relevant-to-you stuff in HYPTUSS to ignore.amittal6 wrote:In terms of using HYPTUSS tool, am not very sure if I am capable enough to do it. Doing it from FT site would just be fine.
Thinking of how can I automate it. Have not yet figured out.
Download and open the HYPTUSS spreadsheet, go to the FT Funds sheet and press Alt-F11. In the VB window that opens click on VBAProject in the left hand pane, enter the password (pleaseletmein) and then open Microsoft Excel Objects then double click Sheet11 (FT Funds), and there is the VBA you need to nick.
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Alternatively you could just use HYPTUSS as is.
![Wink ;)](./images/smilies/icon_e_wink.gif)
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: import latest fund (OEIC) prices in excel
Some stand-alone instructions here that should help get you going in your own spreadsheet using the same VBA code -amittal6 wrote:
In terms of using HYPTUSS tool, am not very sure if I am capable enough to do it. Doing it from FT site would just be fine.
Thinking of how can I automate it. Have not yet figured out.
https://www.lemonfool.co.uk/viewtopic.p ... 75#p258874
Cheers,
Itsallaguess
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: import latest fund (OEIC) prices in excel
P.S. A even easier way is simply to get HYPTUSS and delete all the sheets except FT Funds. That'll give you a working spreadsheet to start with. Ideally you should still go into VB and clear out the other no-longer-needed forms and modules, but it won't hurt if you don't (other than making your .xls a bit bigger than needed).mc2fool wrote:Download and open the HYPTUSS spreadsheet, go to the FT Funds sheet and press Alt-F11. In the VB window that opens click on VBAProject in the left hand pane, enter the password (pleaseletmein) and then open Microsoft Excel Objects then double click Sheet11 (FT Funds), and there is the VBA you need to nick.amittal6 wrote:In terms of using HYPTUSS tool, am not very sure if I am capable enough to do it. Doing it from FT site would just be fine.
Thinking of how can I automate it. Have not yet figured out.
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: import latest fund (OEIC) prices in excel
The HYPTUSS was conceived as a tool to support the HYP methodology (there's a surprisemc2fool wrote:Umm ... well I'm not sure how that answers my question as to why you decided to fetch prices from the FT as opposed to using the existing Yahoo mechanism.kiloran wrote:One of the benefits of using the FT site is that it provides an easy link to a summary page, such as https://markets.ft.com/data/funds/tears ... N45980:GBX
![Wink ;)](./images/smilies/icon_e_wink.gif)
![Smile :)](./images/smilies/icon_e_smile.gif)
Hope that makes a degree of sense. If not, just accept that the decision was made on a whim! Or there is madness in our methods.
--kiloran
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: import latest fund (OEIC) prices in excel
As Lenny Pepperbottom famously said - "It's like that because of the way it is..."mc2fool wrote:
that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism?
Cheers,
Itsallaguess
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: import latest fund (OEIC) prices in excel
I love the way you condensed my 200 words of explanation and reasoning into 9!Itsallaguess wrote:As Lenny Pepperbottom famously said - "It's like that because of the way it is..."mc2fool wrote:
that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism?
Cheers,
Itsallaguess
--kiloran
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: import latest fund (OEIC) prices in excel
Yes.kiloran wrote:...we could have got the prices from Yahoo, but the facility to link to a site to display more information for the fund would have been more complicated due to the different fund symbols. For example, Invesco High Yield Fund (UK) Z (Acc) is 0P0000XBQ0.L in Yahoo but GB00B8N45980 for the FT web page.
Hope that makes a degree of sense.
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
https://finance.yahoo.com/quote/GB00B8N45980.L
https://finance.yahoo.com/quote/0P0000XBQ0.L
Bizarre .............
![Confused :?](./images/smilies/icon_e_confused.gif)
And if you want the latter from the former: https://query1.finance.yahoo.com/v1/fin ... ewsCount=0
-
- Lemon Half
- Posts: 7675
- Joined: November 4th, 2016, 11:20 am
Re: import latest fund (OEIC) prices in excel
What I do is to highlight the whole table on Trustnet, from the Start of the first company name to the "View/Edit" box of the last one. I then copy it into Notepad, and then copy it into a blank spreadsheet in the same workbook. You can then link the cells in the downloaded sheet to the appropriate cells in the sheet that you have set up. It helps if company names are in the same order.amittal6 wrote:Thanks TJH
I have set up one at trustnet earlier.
However I am maintaining an excel sheet - was thinking if I could just import the latest price of a fund. I know manually entering all regular investments on trustnet would be little cumbersome as my investment will grow and entering it manually would also be timeconsuming, hence was thinking how could I just get the fund closing price in my excel.
If it's there any code or google finance sheet trick, I am happy to try that out. Just need you bit guidance
Thanks
Anks
Have a try and see how you get on. The Notepad stage is vital. Once set up you can copy the table from Notepad into the same sheet each time, as long as you don't change investments.
TJH
-
- Posts: 6
- Joined: August 27th, 2020, 10:17 pm
Re: import latest fund (OEIC) prices in excel
Alternatively you could just use HYPTUSS as is.
Thanks mc2fool. I tried but not able to go past the code error issue. Could you suggest?
Code: Select all
' get fund price from FT
' added to 11.72 19Jan20....Alan
Function getFundPrice(fundSymbol)
Dim fundData(2) As Variant
Dim fundPrice
Dim fundCurrency As String
Dim oXMLHTTP As Object
Dim html As Object
Dim myUrl As String
Dim myCount As Integer
Dim dummy As Object
Dim priceCur As Object
Dim price As Object
Dim priceData As String
Set html = New HTMLDocument ' New MSHTML.HTMLDocument
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
myUrl = "https://markets.ft.com/data/funds/tearsheet/summary?s=" + fundSymbol
With oXMLHTTP
.Open "GET", myUrl, False
.send
End With
html.body.innerHTML = oXMLHTTP.responseText
Set priceCur = html.getElementsByTagName("li")
For Each price In priceCur
If InStr(UCase(price.innerHTML), "PRICE ") Then
priceData = price.innerText
fundCurrency = Mid(priceData, InStr(priceData, "(") + 1, 3)
fundPrice = Mid(priceData, InStr(priceData, ")") + 1, 10)
Exit For
End If
Next
fundData(1) = fundPrice
fundData(2) = fundCurrency
getFundPrice = fundData
End Function
-
- Posts: 6
- Joined: August 27th, 2020, 10:17 pm
Re: import latest fund (OEIC) prices in excel
I think the problem in HYPTUSS tool is with below line:
Set html = New HTMLDocument ' New MSHTML.HTMLDocument
What should I set it?
Set html = New HTMLDocument ' New MSHTML.HTMLDocument
What should I set it?