Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Automating retrieval of OEIC prices in Excel

Discussions regarding financial software
iambic
Lemon Pip
Posts: 69
Joined: October 11th, 2018, 1:22 pm
Has thanked: 31 times
Been thanked: 7 times

Automating retrieval of OEIC prices in Excel

#258520

Postby iambic » October 17th, 2019, 4:09 pm

I've unitised my portfolio from the start, which is helpful for seeing overall returns, but as the number of my holdings is gradually increasing, I'd like to also start keeping track of how individual holdings are performing over time. I can do this manually but am looking at automating it to save time (& because who doesn't enjoy tinkering with Excel :) )

I've found a number of useful spreadsheet templates that use Google Finance functions for instance & have played with the Stocks Data Type within Excel itself. These all seem great for getting the current value of shares or investment vehicles which have a ticker symbol like HFEL, TRY, PHP etc. but I also hold some OEIC funds that don't have symbols; like Fundsmith Equity & Vanguard FTSE UK All Share Index Unit Trust.

Does anyone know if it's possible to retrieve the latest prices for these types of investments as well? I've played a bit with getting data from Morningstar into Excel using VBA but it's a bit clunky & I'm sure there must be an easier way.

Many thanks.
Moderator Message:
Moved to Financial Software. - Chris

b0f77
Posts: 37
Joined: February 19th, 2017, 1:54 am
Has thanked: 34 times
Been thanked: 7 times

Re: Automating retrieval of OEIC prices in Excel

#258846

Postby b0f77 » October 18th, 2019, 6:55 pm

I have Google sheets to automate my portfolio valuations by importing price data from the FT site for all my funds as Google Finance does not have price data from them.

What I do is use an "Import HTML" function to import prices into a table sheet like this:

Code: Select all

=IMPORTHTML("https://markets.ft.com/data/funds/tearsheet/historical?s=GB00B4R2F348:GBP","table",1)


This one is importing price data for Vanguard LS60 Inc fund from here:
https://markets.ft.com/data/funds/tears ... R2F348:GBP

You just need to find the FT page with the price data for your fund.

That gives me a table and the first row has the latest closing price from the last business day in a column, and I use that from my other sheets.

If Excel has a similar HTML import function, you could do the same.

You may also be able to get the data like this from another source - for example with L&G I have done the same direct from their site.

It is a bit fragile to the "provider" changing their web site structure, however - so your mileage may vary

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Automating retrieval of OEIC prices in Excel

#258874

Postby Itsallaguess » October 18th, 2019, 9:17 pm

iambic wrote:
I've found a number of useful spreadsheet templates that use Google Finance functions for instance & have played with the Stocks Data Type within Excel itself.

These all seem great for getting the current value of shares or investment vehicles which have a ticker symbol like HFEL, TRY, PHP etc. but I also hold some OEIC funds that don't have symbols; like Fundsmith Equity & Vanguard FTSE UK All Share Index Unit Trust.

Does anyone know if it's possible to retrieve the latest prices for these types of investments as well? I've played a bit with getting data from Morningstar into Excel using VBA but it's a bit clunky & I'm sure there must be an easier way.


I'm not sure how far you got with the Morningstar scrape, but I've just had a quick look at trying to automate something for you using the Financial Times site, which at least enables you to load the URL with the unique fund ID, and I think this will at least get you going to some degree....

You'll need to set up a new Excel sheet with the following in the correct rows and columns, with a VBA command-button next to it that will allow us to launch the VBA code -

https://i.imgur.com/UK3HDxg.png

Note that the 'Fund ID' field is populated using the unique ID from the Financial Times URL for each fund, so hopefully you can find the funds you're hoping to get the prices for via the Financial Times site (example URL here for the Fundsmith Equity page - https://tinyurl.com/y3jvtjq6)...

Once you've populated some sample fund data into Columns A and B from Row 2 downwards, then attach the following code to the command-button -

Code: Select all

Private Sub CommandButton1_Click()

Dim myUrl As String

Set html = New HTMLDocument
 
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
       
Let LastRow = Range("A65536").End(xlUp).Row

For rowsdown = 2 To LastRow

        myUrl = "https://markets.ft.com/data/funds/tearsheet/historical?s=" & Cells(rowsdown, 2)
       
        With oXMLHTTP
            .Open "GET", myUrl, False
            .send
        End With
       
        html.body.innerHTML = oXMLHTTP.responseText
       
        Set spans = html.getElementsByTagName("span")
       

        For i = 0 To spans.Length - 1
           
            If Left(spans(i).innerText, 5) = "Price" Then
                    Cells(rowsdown, 3) = Mid(spans(i).innerText, InStr(1, spans(i).innerText, "(") + 1, 3)
                    Cells(rowsdown, 4) = spans(i + 1).innerText
            End If
        Next i
               
Next rowsdown
               
MsgBox "Fund prices updated"

End Sub


You'll have to enable the HTML Object Library in the VBA Editor (Tools / References), but once you've done that then you should be able to press the command button and get similar price-data to the following snapshot -

https://i.imgur.com/EGKrRI7.png

Hope this helps.

Cheers,

Itsallaguess

PrefInvestor
Lemon Slice
Posts: 590
Joined: February 9th, 2019, 8:24 am
Has thanked: 31 times
Been thanked: 254 times

Re: Automating retrieval of OEIC prices in Excel

#258923

Postby PrefInvestor » October 19th, 2019, 10:46 am

Hi iambic, There are a variety of possible solutions to the problem of automatically updating a portfolio which contains stocks, IT, ETF and OEIC prices and there are many useful posts on this site giving solutions. However some require use of google sheets, others work by screen scraping which can be problematic if the site changes it screen layout and sometimes one finds that the data on a given site isn’t 100% correct (not a lot you can do about that one).

Personally I have never used any of these techniques but have always managed to find a site containing the data that I want in a form that I can easily import into my portfolio spreadsheet either via an excel data query or by simple cut and paste.

For OEICs I have found that investing.com provides what I see as an easy to use solution. Accounts there are free and you can set up multiple portfolios or watchlists containing UK stocks, overseas stocks, investment trusts, ETFs and OEICs.

For an example of an watchlist containing some OEICs see below:-

https://i.imgur.com/cglUWIQ.png

(Sorry I had to post a link as for some reason I couldn’t use the IMG tags on this topic to display the image inline within this post - not sure why ?)

But actually you could have stocks, ETFs and ITs all in the same watchlist and stocks and the prices update in real-time when the relevant market is open. Though I have found that some UK ETF prices seem somewhat delayed as I suspect that they come from a US provider and they only seem to update in the afternoons.

You can also download the watchlist to your computer as a CSV file as a way of importing the data.

Just another alternative for you to think about maybe.

ATB

Pref

Breelander
Lemon Quarter
Posts: 4179
Joined: November 4th, 2016, 9:42 pm
Has thanked: 1000 times
Been thanked: 1855 times

Re: Automating retrieval of OEIC prices in Excel

#258970

Postby Breelander » October 19th, 2019, 2:45 pm

PrefInvestor wrote:(Sorry I had to post a link as for some reason I couldn’t use the IMG tags on this topic to display the image inline within this post - not sure why ?)


The ability to use [IMG] tags is set on a board-by-board basis. This particular board doesn't allow their use, that's all. Links, as you have found, are a usable workaround.

iambic
Lemon Pip
Posts: 69
Joined: October 11th, 2018, 1:22 pm
Has thanked: 31 times
Been thanked: 7 times

Re: Automating retrieval of OEIC prices in Excel

#259136

Postby iambic » October 20th, 2019, 4:53 pm

b0f77 wrote:I have Google sheets to automate my portfolio valuations by importing price data from the FT site for all my funds as Google Finance does not have price data from them.

What I do is use an "Import HTML" function to import prices into a table sheet like this:


Thanks, I haven't played with Google sheets much but it's good to know this is an option.

iambic
Lemon Pip
Posts: 69
Joined: October 11th, 2018, 1:22 pm
Has thanked: 31 times
Been thanked: 7 times

Re: Automating retrieval of OEIC prices in Excel

#259139

Postby iambic » October 20th, 2019, 4:59 pm

Itsallaguess wrote:I'm not sure how far you got with the Morningstar scrape, but I've just had a quick look at trying to automate something for you using the Financial Times site, which at least enables you to load the URL with the unique fund ID, and I think this will at least get you going to some degree....


Thank you so much, this is exactly what I was after - it works a treat! I'd been playing with some vaguely similar VBA, but it was adapted from someone else's code & after hacking about with it for ages I'd massively overcomplicated it to the point of it not working reliably at all... :roll:

Your solution is great though, nice & elegantly simple plus it works for funds & for my holdings with ticker symbols which is ideal.

Much appreciated :-)

iambic
Lemon Pip
Posts: 69
Joined: October 11th, 2018, 1:22 pm
Has thanked: 31 times
Been thanked: 7 times

Re: Automating retrieval of OEIC prices in Excel

#259141

Postby iambic » October 20th, 2019, 5:06 pm

PrefInvestor wrote:For OEICs I have found that investing.com provides what I see as an easy to use solution. Accounts there are free and you can set up multiple portfolios or watchlists containing UK stocks, overseas stocks, investment trusts, ETFs and OEICs.


Thanks Pref, I've got a portfolio set up in Morningstar but haven't kept it as up-to-date as I should have. I'll check out investing.com too as I keep finding holdings that look interesting but that I'm not yet ready to buy, so setting up a couple of watchlists might be a good practice.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Automating retrieval of OEIC prices in Excel

#259145

Postby Itsallaguess » October 20th, 2019, 5:21 pm

iambic wrote:
Itsallaguess wrote:
I'm not sure how far you got with the Morningstar scrape, but I've just had a quick look at trying to automate something for you using the Financial Times site, which at least enables you to load the URL with the unique fund ID, and I think this will at least get you going to some degree....


Thank you so much, this is exactly what I was after - it works a treat!


Great news - and thanks for letting me know it's what you were after.

Cheers,

Itsallaguess

HillManMill
Lemon Pip
Posts: 58
Joined: November 8th, 2016, 8:36 am
Has thanked: 273 times
Been thanked: 22 times

Re: Automating retrieval of OEIC prices in Excel

#273279

Postby HillManMill » December 24th, 2019, 7:16 pm

Hi Guys,

I am trying to get the itsallaguess excel spreadsheet to work using excel 2010 32 bit version on win 10 pc. I have enabled the tools > references> microsoft html object library as advised but nothing seems to be happening. I have tried adding my sample fund id to the end of the html string

e.g. https://markets.ft.com/data/funds/tears ... 00B888FR33 and forming a hyperlink within excel and its opening the web page correctly. Can anyone point me to resolving this one please?

Many thanks

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Automating retrieval of OEIC prices in Excel

#273317

Postby Itsallaguess » December 25th, 2019, 7:59 am

HillManMill wrote:
I am trying to get the itsallaguess excel spreadsheet to work using excel 2010 32 bit version on win 10 pc.

I have enabled the tools > references> microsoft html object library as advised but nothing seems to be happening. I have tried adding my sample fund id to the end of the html string

e.g. https://markets.ft.com/data/funds/tears ... 00B888FR33 and forming a hyperlink within excel and its opening the web page correctly.

Can anyone point me to resolving this one please?


Hi there,

It doesn't sound like you're quite following the process as described in my earlier post - https://www.lemonfool.co.uk/viewtopic.php?f=27&t=19975#p258874

I've just added the fund identity that you're wanting to use to the end of my original list (GB00B888FR33) and everything works fine, as you can see in the two screen-shots linked below which show the process before the VBA Command Button is pressed and then also afterwards -

Before -

https://i.imgur.com/MzNGr2m.png

After -

https://i.imgur.com/bcLA7Z9.png

I'm not sure what you're doing differently to the process detailed in my earlier post, but when you discuss 'hyperlinks' as you do above, then my process doesn't actually need any 'hyperlinks' creating, so I suspect this is an area you might want to concentrate on initially.

So long as you've got the data in the first two columns as shown in my first screen-shot above, and then you've created a 'Command Button' (Active X Control), and then assigned the whole of the VBA routine as shown in my linked post earlier to the 'Command Button', then if you've got Macro's enabled and then press the 'Command Button', the process should work fine.

Do make sure that you're using all the VBA code listed, as there's a scroll-box with the code in, and you'll have to drag and scroll to select all the VBA code before you carry out a COPY/PASTE into the Excel VBA Editor. Here's a screenshot showing the whole VBA code that should be attached to the 'Command Button' -

https://i.imgur.com/HVQaeRM.png

It sounds like iambic was able to follow my process and get things working, so hopefully starting from scratch again might give better results, but please do let me know if there's anything in there that's not as clear as it could be, and I'm more than happy to help out to get you going here.

Also, Merry Christmas!

Cheers,

Itsallaguess

HillManMill
Lemon Pip
Posts: 58
Joined: November 8th, 2016, 8:36 am
Has thanked: 273 times
Been thanked: 22 times

Re: Automating retrieval of OEIC prices in Excel

#273321

Postby HillManMill » December 25th, 2019, 9:27 am

Itsallaguess, Thanks brilliant and speedy response.

Yep you are correct I needed to follow your instructions precisely the error lay in the way in which I was trying to attach the code to the button. The change required was to use the dialogue box that opens when clicking on the command button in design mode to attach the code. I had followed a different process.

Thanks once again.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Automating retrieval of OEIC prices in Excel

#273376

Postby Itsallaguess » December 25th, 2019, 7:55 pm

HillManMill wrote:
The change required was to use the dialogue box that opens when clicking on the command button in design mode to attach the code. I had followed a different process.


Great to know you've got going now, so thanks for reporting back.

I hope you find the process useful.

All the best,

Itsallaguess

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: Automating retrieval of OEIC prices in Excel

#273396

Postby granretire » December 26th, 2019, 9:01 am

Is there any way that funds could be incorporated into your invalueable HYTUS?

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Automating retrieval of OEIC prices in Excel

#273398

Postby Itsallaguess » December 26th, 2019, 9:53 am

granretire wrote:
Is there any way that funds could be incorporated into your invaluable HYPTUSS?


Hi granretire,

It's not technically difficult, but I'd have two reservations to be honest -

1. The tool is primarily aimed at a 'high-yield' audience, and we don't see a great deal of funds being used in the types of portfolio's we're trying to help with regarding the HYPTUSS tool.

2. More importantly, it would introduce an extra on-line data-set that we'd inevitably have to 'keep up with' as these sites change their data-configuration over the years.

As the second point above is becoming a regular maintenance issue just with our existing on-line HYPTUSS data-sets, and taking the first point into account at the same time, I'd be nervous about trying to incorporate this type of new data-set into the main HYPTUSS tool if there wasn't a body of demand for it beyond some outlier users.

With that said, I will caveat the above with the idea that another separate work-sheet in the HYPTUSS tool, where users could perhaps manage their fund holdings separately using this automatic pricing functionality, might be a good half-way-house with this, and I will take this point up with kiloran when we're working towards our next update of the tool some time in the New Year.

Cheers,

Itsallaguess

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: Automating retrieval of OEIC prices in Excel

#273400

Postby granretire » December 26th, 2019, 10:09 am

Thanks. I was thinking more of the lines of it being a separate worksheet anyway - just after I had posted!

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: Automating retrieval of OEIC prices in Excel

#280906

Postby kiloran » January 29th, 2020, 3:23 pm

granretire wrote:Is there any way that funds could be incorporated into your invalueable HYTUS?

Done...... see: viewtopic.php?f=27&t=21542#p280865

--kiloran

wow400
Posts: 4
Joined: March 21st, 2021, 3:28 pm

Re: Automating retrieval of OEIC prices in Excel

#397671

Postby wow400 » March 21st, 2021, 3:35 pm

Sorry to bring up an old post but I'm trying to use the VBA code in a spreadsheet I've got.
Unfortunately I have no idea about VBA but what I'd like to do is to be able to change the coding so that it references different rows & columns (rather than just A & B from row 2 onwards.
The spreadsheet I've got has fund names & ISIN codes from G3 & I3 through to G15 & I15 then the second lot of funds from G25 & I25 through to G & I 37.

Can this be done does anyone know?

Kind regards,
Nic

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: Automating retrieval of OEIC prices in Excel

#398083

Postby kiloran » March 22nd, 2021, 7:42 pm

wow400 wrote:Sorry to bring up an old post but I'm trying to use the VBA code in a spreadsheet I've got.
Unfortunately I have no idea about VBA but what I'd like to do is to be able to change the coding so that it references different rows & columns (rather than just A & B from row 2 onwards.
The spreadsheet I've got has fund names & ISIN codes from G3 & I3 through to G15 & I15 then the second lot of funds from G25 & I25 through to G & I 37.

Can this be done does anyone know?

Kind regards,
Nic

It can be done for sure, but if you have no idea about VBA, you've got a problem!

If you want to have a go at it, the key is to understand how to reference cells. As an example:
fundSymbol = Sheets("FT Funds").Cells(rowsdown, 2)
reads the contents in the cell Row = rowsdown (a variable) and Column = 2 and assigns the celll contents to the variable fundSymbol
So Cell G3 would be referenced by Cells(3,7)
Cell I15 would be referenced by Cells(15, 9)

I think you have two options:
  1. Learn VBA. We can help if (when!) you run into problems
    or
  2. Use the existing "FT Funds" sheet for all of your funds and read the data into your existing sheet by using a look-up. This needs no knowledge of VBA

Good Luck!
--kiloran

wow400
Posts: 4
Joined: March 21st, 2021, 3:28 pm

Re: Automating retrieval of OEIC prices in Excel

#398104

Postby wow400 » March 22nd, 2021, 9:09 pm

Thanks Kiloran - that’s a great help & I’ll have a mess around and see what I can come up with!
Appreciate you taking the time to post.
Kind regards,
Nic


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 6 guests