The Lemon Fool

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

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

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