# Google docs spreadsheet giving #N/A for all Canadian stocks???



## kyboch (Dec 23, 2011)

My google docs spreadsheet that I use to track my investments suddenly started to show #N/A in place of price. Just stopped working 2 days ago. US stocks show up fine. this is the formula =GoogleFinance ("ZRE"; "price") Is anybody else having the same issue? I sent an email to google through the help section but haven't had a response as of yet.


----------



## andrewf (Mar 1, 2010)

Do you have to specific the exchange in the ticker, like TSE:ZRE?


----------



## CanadianCapitalist (Mar 31, 2009)

I noticed this as well. Specifying the exchange doesn't work either but this is only for TSE stocks. Other foreign exchanges seems to work correctly.


----------



## CanadianCapitalist (Mar 31, 2009)

Try this:

=index(importhtml("http://finance.yahoo.com/q?s=XIU.TO","table",2),1,2)

It gives you the previous day's close but it should be good enough for most people. You can probably find other sources to get the current price.


----------



## CanadianCapitalist (Mar 31, 2009)

Or this:

=index(importhtml("http://tmx.quotemedia.com/quote.php?qm_symbol=xiu","table",4), 5, 2)

for current bid value from TSX website.


----------



## kyboch (Dec 23, 2011)

Nope, you can follow the stock symbol with a .TO to signify the TSX ie: ZRE.TO but I haven't had to do that yet. It was working flawlessly until 2 days ago and just started giving the #N/A error. I really don't know what's going on.


----------



## kyboch (Dec 23, 2011)

Hey CC thanks...you are some kind of spreadsheet/internet guru man. Anyways I just checked my spreadsheet and it's all good again, just as quickly as it went haywire it's back to working perfectly!


----------



## kyboch (Dec 23, 2011)

and just as I posted the above, it's back to #N/A...I guess it's something they're working on. I tell you, I have really gotten used to the spreadsheet, it's awesome to see everything in one place. Hope they get it figured out soon....


----------



## andrewf (Mar 1, 2010)

It's not the first time they had issues with their data feeds. Sometimes they don't have quotes for a bunch of ETFs.


----------



## slacker (Mar 8, 2010)

They do seem to come in and out.


----------



## KaeJS (Sep 28, 2010)

This is normal.

Don't change your formula. Just leave it alone and it will fix itself.


----------



## EmJay (Aug 3, 2012)

*Google Docs =GoogleFinance function trouble*

Is anyone having trouble with the =GoogleFinance function for anything listed on a canadian exchange? This function was working fine a few days ago, and hasn't worked since. I did some searching and couldn't find if Google changed the way it works or if they are having some problems on their end.

Here is the function that I am talking about:
http://support.google.com/docs/bin/answer.py?hl=en&answer=155178

How I am currently using it:
=GoogleFinance("CDZ";"price")

Thanks!


----------



## CanadianCapitalist (Mar 31, 2009)

Yes many members are seeing this. See this thread for workaround options though I would personally wait for it to sort itself out.


----------



## EmJay (Aug 3, 2012)

CanadianCapitalist said:


> Yes many members are seeing this. See this thread for workaround options though I would personally wait for it to sort itself out.


Will do, thank you. I guess I didn't look hard enough as I didn't see the thread that mine was merged with. Sorry about that.


----------



## cash (Mar 5, 2011)

*Google Docs Portfolio Tracker*

I'm using this to track my holdings. I've got everything set-up properly, except CLF: 1-5 yr Gov Bond Ladder. I tried typing in CLF.TO, TSE.CLF and CLF. Google Finance can't find it, I can only get it to pull up 'Cliffs Natural Resources'. All my other holdings are in there fine. What am I doing wrong?


----------



## CanadianCapitalist (Mar 31, 2009)

cash said:


> What am I doing wrong?


I'm not sure. Try one of the other methods described in this merged thread. TSX website is usually very reliable.


----------



## cash (Mar 5, 2011)

=index(importhtml("http://finance.yahoo.com/q?s=CLF.TO","table",2),1,2)

Gives me the current market price for CLF, but I still can't get 52 wk high, 52 wk low and the same day % price change.


----------



## CanadianCapitalist (Mar 31, 2009)

cash said:


> I still can't get 52 wk high, 52 wk low and the same day % price change.


You need to find a website that provides you with this data. You can compute the % price change from the TSX data posted in this thread. Not sure about 52 week high/low data. TSX also has this data but you may have to dig around a bit to get the numbers you want.


----------



## zepcom (Oct 11, 2012)

*Canadian Mutual Funds*



CanadianCapitalist said:


> You need to find a website that provides you with this data. You can compute the % price change from the TSX data posted in this thread. Not sure about 52 week high/low data. TSX also has this data but you may have to dig around a bit to get the numbers you want.


I am trying to recreate what you have done with stocks for mutual funds, so I can get a quote for CIG891. Try though I might, I can't seem to get the Google Docs lookup function to work with this site: http://tmx.quotemedia.com/funds-ca-quote.php?qm_symbol=cig891

Can you help? There has to be a way (Google Finance does not carry this fund, don't know why). Thanks


----------



## Phab (Nov 18, 2012)

*TSX data spreadsheet function*



CanadianCapitalist said:


> Or this:
> 
> =index(importhtml("http://tmx.quotemedia.com/quote.php?qm_symbol=xiu","table",4), 5, 2)
> 
> for current bid value from TSX website.


This is a wonderful function. Where can I learn the syntax? I'd like to use it for other data, say retrieving the dividend or perhaps other data.
thank you


----------



## Tawcan (Aug 3, 2012)

Anyone know how to get stocks like REI.UN or D.UN? I tried entering =GoogleFinance("REI.UN.TO";"PRICE") but that entered error. :upset:


----------



## explorer416 (Jun 11, 2010)

Drop the ".TO". Just use =GoogleFinance("REI.UN","PRICE")


----------



## Tawcan (Aug 3, 2012)

Perfect, thank you!


----------



## caronbbc (Mar 27, 2018)

*Google Spreadsheet stock symbols*



kyboch said:


> My google docs spreadsheet that I use to track my investments suddenly started to show #N/A in place of price. Just stopped working 2 days ago. US stocks show up fine. this is the formula =GoogleFinance ("ZRE"; "price") Is anybody else having the same issue? I sent an email to google through the help section but haven't had a response as of yet.


I removed the ".TO" from all my TSX stocks and everything is working again. One problem is for symbols that are duplicated in NY such as TELUS - for TSX, it is "T". In NY, "T" is for AT&T and this is what I get for Telus. Not sure how to get around this problem.


----------



## Spudd (Oct 11, 2011)

caronbbc said:


> I removed the ".TO" from all my TSX stocks and everything is working again. One problem is for symbols that are duplicated in NY such as TELUS - for TSX, it is "T". In NY, "T" is for AT&T and this is what I get for Telus. Not sure how to get around this problem.


Use TSE:T instead.


----------



## NightHawk (Apr 6, 2018)

*TMX listed securities and dividends*

Google sheets is an abomination when it comes to extracting some details about Canadian securities listed on the TMX. It is fine for a lot of functions like "price", but good luck on finding dividends and yields, even using the ImportXML function. Some time you have to provide two different ticker symbols ... one for the price and one for the dividends. Not consistent. Does not appear to be a consistent solution that works.

I will have to track manually for some attributes.:mad2:


----------



## Tawcan (Aug 3, 2012)

NightHawk said:


> Google sheets is an abomination when it comes to extracting some details about Canadian securities listed on the TMX. It is fine for a lot of functions like "price", but good luck on finding dividends and yields, even using the ImportXML function. Some time you have to provide two different ticker symbols ... one for the price and one for the dividends. Not consistent. Does not appear to be a consistent solution that works.
> 
> I will have to track manually for some attributes.:mad2:


You might want to take a look at this:

https://www.tawcan.com/step-step-guide-make-google-spreadsheet-dividend-portfolio-template/


----------

