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
Gerry557
Lemon Quarter
Posts: 2006
Joined: September 2nd, 2019, 10:23 am
Has thanked: 172 times
Been thanked: 544 times

Re: Automating retrieval of OEIC prices in Excel

#398148

Postby Gerry557 » March 23rd, 2021, 7:07 am

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:

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


I have just tried cutting and pasting that code into my sheet to test so I could add in my own funds but it didn't work.

I don't know if its an old function or references something that has moved.

Anyone else can confirm if this still works

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

#398205

Postby kiloran » March 23rd, 2021, 9:56 am

Gerry557 wrote:
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:

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


I have just tried cutting and pasting that code into my sheet to test so I could add in my own funds but it didn't work.

I don't know if its an old function or references something that has moved.

Anyone else can confirm if this still works

I just tried this and it works fine

--kiloran

Gerry557
Lemon Quarter
Posts: 2006
Joined: September 2nd, 2019, 10:23 am
Has thanked: 172 times
Been thanked: 544 times

Re: Automating retrieval of OEIC prices in Excel

#398228

Postby Gerry557 » March 23rd, 2021, 10:43 am

Thanks kiloran

Having rechecked it I get a message that it will overwrite some other cells

I have tried it again in some spare space and get a list of prices approx over a month. It gives me something to work off although I think was expecting just the latest price not a table.

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

Re: Automating retrieval of OEIC prices in Excel

#398354

Postby wow400 » March 23rd, 2021, 5:02 pm

kiloran wrote:
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


Thanks Kiloran - with your info I managed to reference the correct cells!
A couple of odd outputs - on 2 the GBX is blue & underlined (a la hyperlink) and one of the prices is again blue & underlined so a bit more digging is needed but I'm on the correct track now.
One last VBA type question if I may - the ISIN's are all in a column except there's a break of 9 rows with text in. It hasn't made any difference to the VBA output but I was wondering how you could set up a reference for cells (3,7) to cells (15,7) then continuing with cells (24,7) to cells (34,7)?
Does that make any sense?

Anyway, thanks again,
Nic

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

Re: Automating retrieval of OEIC prices in Excel

#398357

Postby wow400 » March 23rd, 2021, 5:18 pm

Actually, I’m just being lazy - I’ll go searching how to reference cells!


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 10 guests