import latest fund (OEIC) prices in excel

[amittal6]

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

[tjh290633]

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

[amittal6]

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

[mc2fool]

I don't know much about screenscraping nor vba coding, but can try if somebody can help.

This is what you want: http://lemonfoolfinancialsoftware.weebly.com/simple-yahoo-price-scrape.html

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).

[Itsallaguess]

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.

Hi there,

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 -



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.weebly.com/hyp-top-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

[dspp]

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

[mc2fool]

We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago

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).

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.

[kiloran]

We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago

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).

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.

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
Yahoo does not have this

--kiloran

[amittal6]

We incorporated a facility for obtaining Fund prices into our HYPTUSS tool a while ago

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).

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.

[amittal6]

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!

[mc2fool]

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

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.

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?

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.

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.

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.

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.

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.

Alternatively you could just use HYPTUSS as is.

[Itsallaguess]

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.

Some stand-alone instructions here that should help get you going in your own spreadsheet using the same VBA code -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=19975#p258874

Cheers,

Itsallaguess

[mc2fool]

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.

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).

[kiloran]

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

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.

The HYPTUSS was conceived as a tool to support the HYP methodology (there's a surprise ) and therefore was intended to handle normal shares. It has links to charts, forecast yields, dividends, RNS, etc. If shares and funds/OEICs were mixed, prices could be retrieved, but much of the other functionality would break (nicely if Itsallaguess coded it, or calamitously if I coded it ) since such information is often not available for funds (or perhaps is available from other sources). We therefore used a separate Funds sheet. For this, 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. If not, just accept that the decision was made on a whim! Or there is madness in our methods.

--kiloran

[Itsallaguess]

that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism?

As Lenny Pepperbottom famously said - "It's like that because of the way it is..."

Cheers,

Itsallaguess

[kiloran]

that's curious ... why did you decide to get fund prices from the FT rather than just using the existing Yahoo mechanism?

As Lenny Pepperbottom famously said - "It's like that because of the way it is..."

Cheers,

Itsallaguess

I love the way you condensed my 200 words of explanation and reasoning into 9!

--kiloran

[mc2fool]

...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.

Yes. Fair enuff ......................................... although (and I've only just discovered this myself) it seems that Yahoo does understand ISINs. Indeed, going by the ISIN gets the latest price, whereas going by the Yahoo symbol seems to be stuck on 8-July. At least for that fund.

https://finance.yahoo.com/quote/GB00B8N45980.L
https://finance.yahoo.com/quote/0P0000XBQ0.L

Bizarre .............

And if you want the latter from the former: https://query1.finance.yahoo.com/v1/finance/search?q=GB00B8N45980&newsCount=0

[tjh290633]

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

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.

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

[amittal6]

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


[amittal6]

I think the problem in HYPTUSS tool is with below line:

Set html = New HTMLDocument ' New MSHTML.HTMLDocument

What should I set it?