Jump to content

Stock Quotes in Google Spreadsheet


zarley

Recommended Posts

I've been using a Google spreadsheet to track my portfolio for several years.  It's nice because it will automatically update prices and position sizes and my relative performance.  But, I've noticed a problem with getting price data for Fairfax over the past few days.  It apparently won't fetch fairfax price data, returning N/A instead of price.  Obviously, this screws up my whole spreadsheet. 

 

Questions:

 

Anyone else using a google spreadsheet in this way? 

If so, have you experienced this problem with Fairfax or any other security?

Any workarounds for this?

Are the other options for this sort of thing? 

 

Thanks in advance,

 

 

 

 

Link to comment
Share on other sites

I've had the same problem intermittently today for Canadian quotes. Expecting it to be just a temporary glitch.

 

Hmmmm . . . thanks Liberty.  I guess it's good news that it seems to be a general bug.  Hopefully it will get fixed soon enough.

 

A couple days with a broken spreadsheet has been oddly irritating.  How am I supposed to go about my day if I don't know my year to date relative out-performance to the second decimal?  :)

Link to comment
Share on other sites

yep, I've been having the same problem with FFH and with the index.  Here's my fixes for it until they get it fixed:

 

for inx:

=ImportData( "http://download.finance.yahoo.com/d/quotes.csv?s=%5EGSPC&ql=1&f=l1"&"&workaround="&INT(NOW()/TIME(0;2;0))&REPT(GoogleFinance("GOOG");0))*GoogleFinance("CURRENCY:CADUSD")

 

for FFH (with conversion to USD):

=ImportData( "http://download.finance.yahoo.com/d/quotes.csv?s=FFH.TO&ql=1&f=l1"&"&workaround="&INT(NOW()/TIME(0;2;0))&REPT(GoogleFinance("GOOG");0))*GoogleFinance("CURRENCY:CADUSD")

 

Link to comment
Share on other sites

incidentally, if you want to pull the latest S&P500 data including dividends, use this:

 

=(Index(ImportHtml("http://www.standardandpoors.com/indices/sp-500/en/us/?indexId=spusa-500-usduf--p-us-l--", "table", 7),4,7))

 

I also have it update based on the S&P500 so the TR updates in real time by taking the difference between the TR and the normal return and adding it to the current change in S&P500 ytd:

 

=(Index(ImportHtml("http://www.standardandpoors.com/indices/sp-500/en/us/?indexId=spusa-500-usduf--p-us-l--", "table", 7),4,7)) - (Index(ImportHtml("http://www.standardandpoors.com/indices/sp-500/en/us/?indexId=spusa-500-usduf--p-us-l--", "table", 7),6,7))+E3

 

where E3 is current S&P ytd %.

Link to comment
Share on other sites

Thanks racemize.  I messed around with some Yahoo Finance work arounds for about 10-15 minutes.  Must have gotten some of the syntax wrong, because I didn't get that working right either.

 

Modifying your scripts a little bit got me what I needed.  Thanks

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...