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

Googlefinance() with SEDOL?

Discussions regarding financial software
yorkshirelad1
Lemon Slice
Posts: 906
Joined: October 5th, 2018, 1:40 pm
Has thanked: 176 times
Been thanked: 299 times

Googlefinance() with SEDOL?

#359347

Postby yorkshirelad1 » November 23rd, 2020, 4:44 pm

I've got a list of SEDOL codes that I'd like to get prices into a spreadsheet.
I'm looking at Googlefinance() in a Google sheet.
It seems that Googlefinance() wants a ticker.
Is there a way to use SEDOL codes with Googlefinance() or do I have to translate SEDOL into tickers (and if so how? I've seen something called importhtml).
I've done a bit of Googling, including several references to lemons, but I'm not sure which the best way is, and it does seem to change (obviously, over time, with the technologies offered etc).
I'm happy with functions, and sheets and Excel etc, so a few pointers for the best way at the moment to get me started would apreciated. I'll report back if I get successful.

Equally, if there's a better way (website) to get prices for a list of SEDOL codes (preferably allowing building a portfolio, so either a website or a spreasheet), all offers welcome.
TIA

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 210 times

Re: Googlefinance() with SEDOL?

#359558

Postby torata » November 24th, 2020, 10:25 am

It's less robust because it replies on websites which can be re written, but you can scrape from webpages into google docs, using =IMPORTHTML()

I use it for a google SS that includes shares, funds, pref shares, foreign funds in other countries/currencies, etc

I use a separate tab for the importhtml info and tend to use two different sources (morningstar, yahoo, etc) so that if one website gets changed, it's easy to flip to the other source.

There's a brief explanation here https://www.lemonfool.co.uk/viewtopic.php?f=27&t=25535#p345868 which is for IT premia, but the concept is the same.

torata

yorkshirelad1
Lemon Slice
Posts: 906
Joined: October 5th, 2018, 1:40 pm
Has thanked: 176 times
Been thanked: 299 times

Re: Googlefinance() with SEDOL?

#359689

Postby yorkshirelad1 » November 24th, 2020, 5:17 pm

torata wrote:It's less robust because it replies on websites which can be re written, but you can scrape from webpages into google docs, using =IMPORTHTML()
(snip)
torata


Many thanks

yorkshirelad1
Lemon Slice
Posts: 906
Joined: October 5th, 2018, 1:40 pm
Has thanked: 176 times
Been thanked: 299 times

Re: Googlefinance() with SEDOL?

#359691

Postby yorkshirelad1 » November 24th, 2020, 5:24 pm

I did some investigating into this last evening. It’s been interesting, but I don’t think I can’t make much use of it given the work involved and for what I want to achieve and the timescales. It would have been nice, but I can do the analysis another way. However, the excursion in to the murkier world of shareprice data into spreadsheets and screenscraping was interesting, and useful.

I didn’t find a relatively available easy (free) way of converting SEDOL to tickers. I ended up doing it manually. I guess there are subscription sites that will do it.

I had a look at googlefinance() and really liked it. The ability to supply a ticker for the latest price was very straightforward and is great. It seems adding a market location to the ticker (e.g. LON:GSK) would be sensible. The ability to pass a date as an argument is excellent (so creating a list of shares with today’s price, then creating a second column which has the share price on another date is just genius). The downside (for me) is the difficulty in (or lack of) getting fund prices. There are several people around who have discovered the magic tricks to eliciting fund prices with googlefinance() (e.g. =GoogleFinance("MUTF_GB:VANG_FTSE_UK_BSJM2F") of this parish, but it may not be available any more), but it seems to be not straightforward.

I experimented with =IMPORTXML() from Googlesheets, and this is quite useful, but it would require a lot of work, and may be at the dehest of website designers changing their page layout to upset the fetching. If I was going to be using the data on a stable and long-term basis, I’d probably do the work, and make the tweaks as webpages were changed, but I wanted to do some discrete analysis of some data, which means that it would be a lot of work for not much very benefit (when I can do the analysis another way).

I liked this page for info on googlefinance()
https://support.google.com/docs/answer/3093281?hl=en-GB
and this page for doing the screensraping (with IMPORTXML)
https://medium.com/makingofamillionaire/how-to-create-your-own-stock-tracker-with-google-sheets-cd31193145bb
There may be other (better) sources

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Googlefinance() with SEDOL?

#359694

Postby Itsallaguess » November 24th, 2020, 5:30 pm

yorkshirelad1 wrote:
The downside (for me) is the difficulty in (or lack of) getting fund prices.


Are you dead-set on having to get fund-prices with Googlefinance()?

I only ask because if you're happy using Excel then this solution might help you -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=19975#p258874

Cheers,

Itsallaguess

yorkshirelad1
Lemon Slice
Posts: 906
Joined: October 5th, 2018, 1:40 pm
Has thanked: 176 times
Been thanked: 299 times

Re: Googlefinance() with SEDOL?

#360050

Postby yorkshirelad1 » November 25th, 2020, 6:09 pm

Itsallaguess wrote:
yorkshirelad1 wrote:
The downside (for me) is the difficulty in (or lack of) getting fund prices.


Are you dead-set on having to get fund-prices with Googlefinance()?

I only ask because if you're happy using Excel then this solution might help you -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=19975#p258874

Cheers,

Itsallaguess


Nice! Thank you! I got it working after a bit of tweaking with Excel etc. Once I'd figured out how to tweak some of the FundIDs e.g.

GB00B801BG18:GBX
BLND:LSE
DGE:LSE
EXPN:LSE
GB00B469J896:GBX
IE0002458671:USD

it works well.
If I was using it for a relatively stable portfolio for monitoring prices, I'd definitely be using it. It's a fair amount of work setting it up for the project I have in hand (assessing two snapshots of someone else's portfolio), for not a lot of added benefit. When I've got this mini project out of the way, I might implement this for my own portfolio (esp my ISA which tends to be fairly stable).


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 3 guests