How to get current stock prices into Excel

capital0ne

Use a Google Drive account, create a spreadsheet then you can get prices with this:

=GoogleFinance(B46,"price")

where B46 has the name of the share eg LON:RDSB for Shell B shares

GoogleFinance function has many options including PE, historic prices and so on.

And Google sheets work exactly like excel - to easy!

Reluctantpensioner

masonic wrote: »

I maybe hijacking the OP's thread with a tangential question, but If I'm not mistaken neither Trustnet, nor any other portfolio tool supporting UK investments, is able to track the performance of a portfolio that is not static over the timeframe being measured. Is that not the case?

Morningstar?
It certainly gives a gain/loss report for assets you have held historically (which you can then copy/paste to munge a bit more).
The portfolio report is only partially useful. The graphs of portfolio performance are for assets actually held/sold over time, but the graph is very simplistic (best used to compare yourself vs. a benchmark).
Individual asset performance is just raw data, not portfolio weighted.

masonic

Reluctantpensioner wrote: »

Morningstar?
It certainly gives a gain/loss report for assets you have held historically (which you can then copy/paste to munge a bit more).
The portfolio report is only partially useful. The graphs of portfolio performance are for assets actually held/sold over time, but the graph is very simplistic (best used to compare yourself vs. a benchmark).
Individual asset performance is just raw data, not portfolio weighted.

Thanks for the suggestion, I remember trying out Morningstar before, but it was perhaps 10 years ago so I'll give it another look.

RomfordNavy

capital0ne wrote: »

Use a Google Drive account, create a spreadsheet then you can get prices with this:

=GoogleFinance(B46,"price")

where B46 has the name of the share eg LON:RDSB for Shell B shares

GoogleFinance function has many options including PE, historic prices and so on.

And Google sheets work exactly like excel - to easy!

Got the Google drive setup and created a google sheet but having trouble finding the correct ticker for the following:

• Troy Trojan Fund X Accumulation

• Baillie Gifford Japanese Sml Cos B NAV Acc

• Baillie Gifford Japan Trust

• L&G Pharma Breakthrough UCITS ETF (GBP)

• Legal & General Global Health & Pharmaceuticals Index Trust I Class Accumulation

Linton

RomfordNavy wrote: »

Progress update:

Gaier Quotes Add-in partially works but not able to price the following funds:

• L&G Pharma Breakthrough UCITS ETF (GBP)
• Legal & General Global Health & Pharmaceuticals Index Trust I Class Accumulation
• Troy Trojan Fund X Accumulation
• Baillie Gifford Japanese Sml Cos B NAV Acc
• Baillie Gifford Japan Trust

Still, works for the other 80% fine so that saves me some time with the valuation.

Gaiersoft picks up Baillie Gifford Japan Small Companies B Acc for me with no problems. Are you using the correct code: F0GBR04RMW? I dont hold any of your other funds.

RomfordNavy

Linton wrote: »

Gaiersoft picks up Baillie Gifford Japan Small Companies B Acc for me with no problems. Are you using the correct code: F0GBR04RMW? I dont hold any of your other funds.

Thanks @Linton, I have now got the two BG Japan funds included. Just got Troy Trojan and the two L & G Pharma funds to identify.

Linton

Have you used the new Gaiersoft tool to lookup the code? https://gaiersoftware.com/FinancialInstrumentSearch , it works for all my funds.. The other possibility is that I have noticed that the MSN codes seem to be the same as Morningstar's - the ones that appear in the fund web page address.

RomfordNavy

Linton wrote: »

Have you used the new Gaiersoft tool to lookup the code? https://gaiersoftware.com/FinancialInstrumentSearch , it works for all my funds.. The other possibility is that I have noticed that the MSN codes seem to be the same as Morningstar's - the ones that appear in the fund web page address.

Have now found Financial Instrument codes for all of the required funds. Answer was to search for the on MorningStar and then extract the FI code from the URL as that doesn't seem to actually be displayed on any of the pages anywhere.

Tried the new Gaiersoft link you suggested but that didn't find any more than the Excel Add-on did.

For the record Financial Instrument codes for these are:

• F00000ZTV1 - Troy Trojan X Acc
• F0GBR06IK3 - L&G Global Health & Pharma Index I Acc (Accumulation)
• BIGT L&G Pharma Breakthrough UCITS ETF (GBP)

By the way GoogleFinance doesn't find these, the GaierSoftware Add-on finds more than GoogleFinance does.

AlwaysLearnin

In case useful to anyone else, I found something online the other day that I adapted for the Vanguard Lifestrategy 80 Acc price in Google Sheets:

=value(SUBSTITUTE(query(IMPORTXML("https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results/v/vanguard-lifestrategy-80-equity-accumulation","//span[@class='bid price-divide']"),"select* limit 1",0),"p","",1))/100

Seems to work okay so far, and can obviously link Google Sheets in to Excel as required.

Haven't tried it, but presume it could also be adjusted for other funds too.

MarkFromCornwall

AlwaysLearnin wrote: »

=value(SUBSTITUTE(query(IMPORTXML("https://www.hl.co.uk/funds/fund-discounts,-prices--and--factsheets/search-results/v/vanguard-lifestrategy-80-equity-accumulation","//span[@class='bid price-divide']"),"select* limit 1",0),"p","",1))/100

Thanks. Very, very useful for me (after changing 80 to 40 and 'accumulation' to 'income').