How to get current stock prices into Excel

RomfordNavy

Anyone know if there is any way to get current stock/fund/trust prices into a cell of an Excel spreadsheet? Would make valuations of the whole portfolio an awfull lot easier if I could automatically download price updates..

msallen

If you're using the latest version of Excel it's a doddle.

Type the name or ticker of the stock into a cell (sometimes you have to try variations for Excel to recognise it. Try "XLON:JMG" (one of mine so I know this works - typically seen written as JMG.L).

Select the cell then select the Data ribbon from the topmost menu, then click on Stocks and after a moment working out what you mean Excel will replace the text you typed with a Stocks data object in the cell. You can then refer to that cell from elsewhere and query it's price along with a number of other facts (such as Market cap, P/E ratio, 52 week high/low etc etc)

RomfordNavy

msallen wrote: »

If you're using the latest version of Excel it's a doddle.

Unfortunately I have Excel 2013 which doesn't seem to support that.

masonic

RomfordNavy wrote: »

Unfortunately I have Excel 2013 which doesn't seem to support that.

In that case, perhaps upgrade your spreadsheet to Google Sheets:
https://forums.moneysavingexpert.com/discussion/4810901/uk-fund-prices-in-google-docs

Heedtheadvice

I have sometimes postec on the site as response to 'can an expert tell me how to.....'


Now there are things that Excel (and others) can do (all together in one workbook) that are not necessarily easy to achieve elsewhere in one package, or do some individual analysis/printing or linking to data in or out of other apps (databases, document types etc.) but the need for those is not that common outside the professional world.


So I would ask if you really want to go that route or something like the excellent portfolio tool on Trustnet (that has some good investment analytical/charting tools too) would not better fit your need?

masonic

Heedtheadvice wrote: »

So I would ask if you really want to go that route or something like the excellent portfolio tool on Trustnet (that has some good investment analytical/charting tools too) would not better fit your need?

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?

RomfordNavy

Heedtheadvice wrote: »

I have sometimes postec on the site as response to 'can an expert tell me how to.....'


Now there are things that Excel (and others) can do (all together in one workbook) that are not necessarily easy to achieve elsewhere in one package, or do some individual analysis/printing or linking to data in or out of other apps (databases, document types etc.) but the need for those is not that common outside the professional world.


So I would ask if you really want to go that route or something like the excellent portfolio tool on Trustnet (that has some good investment analytical/charting tools too) would not better fit your need?

There might be alternative ways to achieve a general valuation but if possible I would prefer to do my own calculations in Excel. As an example where multiple purchases of the same fund have occurred I like to calculate what I term "Annualised Return on Capital Employed".

I wonder if I could use Trustnet Portfolio too create a page from which I could download all the data I require, copy that to a seperate Excel worksheet and extract each price from there?

RomfordNavy

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?

Precisely my problem.

RomfordNavy

Have just downloaded the Gaier Software, QuotesAddIn64..xll Add-on which looks like it might just work. Would have preferred to do this natively but until I learn how to do that this might be a good interim solution.

Heedtheadvice

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?

I believe you are correct Masonic. I too do not use Trustnet's portfolio tool for that purpose. If it is charted it only (AFAIK) can only give history of the portfolio as currently entered rather than say what one's portfolio was one year ago vs today or other dynamic changes such as what Romford wishes in a spreadsheet to give actual annualised data for holdings over time.
[If it can by it would be good to hear from somebody!]


That is probably best achieved by storing portfolio asset valuations (in whatever granular size one desires) with dated changes at buy/sell time of values and costs in a database, which can easily be a database table within Excel, then that used for calculations. Being stand alone it is then not limited by what platforms give where one has to accept inflexible solutions.

I do a similar but simpler exercise in Excel by just recording overall valuations periodically. Not the best method but just copy data tables from a portfolio web page and paste into the spreadsheet considering it is not worth the effort to link the data for an infrequent task.


No doubt data could be linked from a trustnet portfolio or similar but a drawback is when the whim of the website owner causes a page change and the link fails!


It is a case of horses for courses. Where one wishes to have geographic or asset type analysed for a whole portfolio then Trusnet is good and would not be simple to replicate in a workbook.


But if it can be done......somebody tell us, either way.


Romford, it would be good to hear how you get on!

RomfordNavy

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.