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

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.