Hello
I sometimes struggle to get the correct ETF
to show up in excel for the ticker symbol copied from IE.
example
ISF
DEM
Any tips
It Depends on where you get your data from. The Microsoft Excel Stocks function is only available with Office 365 and quite frankly it is crap. Google sheets is much better. The secret is to include the stock exchange alongside your ticker.
Lets say you open a google docs spreadsheet and the first row is the titles for your columns. For example you can use the following 10 column heading names. The important one based on your question will be the Market heading. That is the google finance abbreviation for which stock exchange to use
Here is the full list supported by google: Disclaimer – Google Finance
Symbol, Market, Company / Stock Name, Currency, Current Price, Close Price Yesterday, Price Change, % Change, 52-week Low Price, 52-week High Price
So “Symbol” will be in cell A1, “Market” will be cell A2 and so on up to A10
Your data entry will then start in row 2 downwards. You will enter your ticker in in A2 and the exchange code in B2 the rest of the row onwards will then self populate with formulas. which I will give you listed here
Symbol, Fill in your ticker
Market, Fill in your stock exchange ex LON, NYSE, NASDAQ etc
Stock Name, Use this formula: =IF(ISBLANK(A2), “-”, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “name”))
Currency, Use this formula: =IF(ISBLANK(A2), “-”, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “currency”))
Current Price, Use this formula: =IF(ISBLANK(A2), 0, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “price”))
Close Price Yesterday, Use this formula: =IF(ISBLANK(A2), “-”, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “closeyest”))
Price Change, Use this formula: =IF(ISBLANK(A2), 0, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “change”))
% Change, Use this formula: =IF(ISBLANK(A2), 0, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “changepct”)/100)
52-week Low Price, Use this formula: =IF(ISBLANK(A2), “-”, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “low52”))
52-week High Price, Use this formula: =IF(ISBLANK(A2), “-”, GOOGLEFINANCE(IF(ISBLANK(B2), A2, B2&“:”&A2), “high52”))
If you did it correctly it will look something like this:
Feel free to add more columns or delete any of the above examples depenidning on what you want to achieve
Note that on the London Stock Exchange some funds are traded in Pound and others in Pence, hence me adding the currency column
Hope this helps; I’m a bit bored at work hence I went a bit overboard with this post
2 Likes
Hi and thanks
I got to converting my excel sheet to google sheets all
was well until SGLN.
After searching it is a known fault and no solution is forthcoming.
Still looking for a stable and complete solution.
Yeah, it a bit of a PITA. Google does not list that particular ticker, but yahoo does as SGLN.L
There are ways to pull Yahoo data on Google sheets but you have to jump through a lot of hoops to get it to work and at best it is quite flaky most of the times. Just not worth the effort.