How to import stock and fund prices in excel

coyrls

amittal6 said:

coyrls said:

There are multiple options for creating a dummy portfolio, some are more amenable to cut and paste than others.  As I say Morningstar works for me.

Thanks. This is not about creating dummy portfolio! Rather about how to import the value of funds in the excel.

I know, my suggestion was that you create a dummy portfolio and cut and paste it into a tab on your spreadsheet.  One of the considerations about what service to use to create a dummy portfolio is how easy it is to cut and paste to a spreadsheet.

John_

amittal6 said:

Linton said:

Or an ft.com portfolio

I have done it. Point is how to reflect the values in excel in someway - except manually entering those regular investment transactions.

If you subscribe to Bloomberg you get the excel add-in included which lets you import prices.

Chordeiles

I never seriously used an online portfolio tool (until I read this thread).  I always made do with my broker's view, or Microsoft Money/AceMoney and (especially at times of "portfolio upheaval") a spreadsheet.
Now I tried the Investors Chronicle portfolio tool (one may need to be a subscriber to get this, but I wouldn't consider NOT being a subscriber !) and I love it.  I love the way that it shows the dividends that should get paid.  And I love the way it handles splits.  I look forward to seeing how it copes with a fund that was subject to "mandatory redemption" (i.e. was wound up) and for which my broker can't properly explain the small payments that still arrive in dribs and drabs !
But I was despairing of the way my portfolio is tabulated by Investors Chronicle, pretty well impossible to copy and paste into a spreadsheet.  Then I noticed "export portfolio" (under "actions") and a .CSV file is presented ready to drop into my spreadsheet.  Just perfect.

UncleK

I've created a Google Sheet (called Prices). For stocks I just need cells like =GOOGLEFINANCE("LON:SHEL","price"), for exchange rates =GOOGLEFINANCE("GBPEUR"), for some funds =GOOGLEFINANCE("LON:VWRP","price") but for others I need to scrape the FT site so =IMPORTXML("https://markets.ft.com/data/funds/tearsheet/summary?s=gb00bmn91t34:gbp","/html/body/div[3]/div[2]/section[1]/div/div/div[1]/div[2]/ul/li[1]/span[2]").
(This scraping often fails so I need to cut them to notepad, refresh the blank cell and the paste them back again. I only have two cells like that so can live with it but if someone has a better way I would be delighted to hear of it.)

I then linked this to my Excel sheet using Power Query Editor and now I just refresh it as I need it, although you set it to it automatically.
Re: Power Query Editor, this is where someone else needs to help. It's been so long since I did this and it was a one off, such that I can't remember the details - sorry to be so useless. Anyone know how to link Google Sheets with Excel?

Cheers

Chordeiles

Having enthused about the way that the Investors Chronicle portfolios export data in .CSV form, I should now point out some "wrinkles" I noticed.

Firstly, in the case of certain managed funds, both the IC and FT show a price that is reflected in the portfolio view, but my broker (ii) has a more precise price.  Bizarrely the total holding value in the IC portfolio is based on the more precise price that IC won't show me !  For instance, "Fundsmith Equity I Acc" today is shown as £7.03 on IC and FT, but ii have £7.0326 which is actually the value that the IC portfolio has used to calculate my holding value.  So the most precise price available in the exported data can in every case be got by dividing the holding value by the number of units I hold.

Secondly, in the case of one ETF (SJPA:LSE:GBX) the FT and IC today show a price of £42.00, but yesterday had a more precise price (£42.38) that matches the price that ii show.  Once again the IC portfoilio shows the "rounded" price (even yesterday when it differed from what the IC page for this ETF was showing) but bases the total holding value on the more precise price.  So once again the best resolution of price information can be got from the downloaded data by dividing the total holding value by the number of shares held.   But I'm still a bit alarmed that the price shown on the portfolio screen view is so drastically "rounded".  And the fact that the underlying price information changed over a Saturday night !

MK62

UncleK said:

I've created a Google Sheet (called Prices). For stocks I just need cells like =GOOGLEFINANCE("LON:SHEL","price"), for exchange rates =GOOGLEFINANCE("GBPEUR"), for some funds =GOOGLEFINANCE("LON:VWRP","price") but for others I need to scrape the FT site so =IMPORTXML("https://markets.ft.com/data/funds/tearsheet/summary?s=gb00bmn91t34:gbp","/html/body/div[3]/div[2]/section[1]/div/div/div[1]/div[2]/ul/li[1]/span[2]").
(This scraping often fails so I need to cut them to notepad, refresh the blank cell and the paste them back again. I only have two cells like that so can live with it but if someone has a better way I would be delighted to hear of it.)

Have a look at mufunds - a Google Sheets add-on     

https://workspace.google.com/marketplace/app/µfunds/274036499952

.....it grabs all the fund prices for you - all you need is the ISIN code for the fund

EG    "=muFunds("nav",GB00BZ82ZV81)"  in a cell returns the latest price for Vanguard Global Equity Income INC into that cell.

UncleK

Thanks for that, MK62. The instructions are a bit flakey but I eventually found something from the author saying that everything has to be in quotes, so even the one you quoted has to look like =muFunds("nav","GB00BZ82ZV81") to work so with that I am cooking on gas - finally..................!

MK62

UncleK said:

Thanks for that, MK62. The instructions are a bit flakey but I eventually found something from the author saying that everything has to be in quotes, so even the one you quoted has to look like =muFunds("nav","GB00BZ82ZV81") to work so with that I am cooking on gas - finally..................!

Yes, that's correct, my bad.......I actually reference the ISIN from another cell so quotes not needed then.....

ie put GB00BZ82ZV81 in cell D3, the formula is then =muFunds("nav",D3)

I suspect that the add-on is probably using the same IMPORTHTML/IMPORTXML type scraping in the background, but it certainly makes everything simpler and neater.

For anyone who prefers "local" spreadsheets, there is a similar extension for Libreoffice Calc - Financial Market Extension - https://github.com/cmallwitz/Financials-Extension - does pretty much the same thing but has a few more data options......I only have an old version of Excel, so don't really use that any more, but I expect that there will be something similar for that too.

UncleK

It may well, MK62, but so far, it seems to be more reliable than the scraping the FT site I was doing before. Just a bit of googling shows that the Google Finance site is not well regarded so I am lucky to be getting most of the information I need from there. Long may it last - and thanks again.

talexuser

I tried mufunds with the ISIN in a specific cell and you still need quotes in the formula for the cell designation for it to work, otherwise you get Error Loading message. Unfortunately the prices shown bore no relation (not even close)  to the latest prices on Morningstar for the first 3 trusts in my portfolio. So back to copy and paste for the whole portfolio.