Author Topic: Google spreadsheet tips and tricks  (Read 37679 times)

bargainman

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 757
Google spreadsheet tips and tricks
« on: February 02, 2013, 08:29:31 AM »
After a great post by meiroy here:
http://www.cornerofberkshireandfairfax.ca/forum/investment-ideas/aig-american-international-group/msg101959/#msg101959

I thought I'd start a thread to aggregate some info on google spreadsheet since I use it to track my investments too.  First a copy and paste from meiroy's excellent post:


Quote
BTW I just figured out how to get correct closing values for options and warrants from Yahoo Finance into Google Docs for anyone who needs it.

Here are two examples.

For AIG-WT:

=Substitute(importXML("http://finance.yahoo.com/q?s=aig-wt&ql=1","//span[@class='time_rtq_ticker']"),"*","")

And as an example for AIG Jan 2015 40.000 call:

=Substitute(importXML("http://finance.yahoo.com/q?s=AIG150117C00040000","//span[@class='time_rtq_ticker']"),"*","")

It works for other warrants and options as well, just put in the correct URL.

Now just one substitution of my own..  I find you only need this in the case of options.  In the case of warrants you can use the following:

=ImportData("http://finance.yahoo.com/d/quotes.csv?s="&A19&"&f=l1")

Where the cell A19 would contain:
AIG-WT
BAC-WTA
BAC-WTB
ROICW

etc.

I've also found a great resource for google spreadsheet imports from yahoo finance here:

http://dubd.wordpress.com/2010/05/10/google-docs-stoxpage/

It gives the list of all values you can import into google.  The one I probably use the most is "y" which is the dividend yield, which for some reason google finance does not give you.

so an example is:

=ImportData("http://finance.yahoo.com/d/quotes.csv?s="&A6&"&f=y")

where A6 contains the ticker like say: ROIC


bargainman

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 757
Re: Google spreadsheet tips and tricks
« Reply #1 on: February 02, 2013, 08:34:13 AM »
Hmm I realized I kind of put the cart in front of the horse..  Let's back up a bit...

to use google spreadsheet's built-in finance commands, here is what you need to know:

http://support.google.com/drive/bin/answer.py?hl=en&answer=155178

Click on the "View a list of common attributes" link and you're all set to go.

In my spreadsheet cells I have:

for example:

=GoogleFinance($A3,B$1)

The column header "B$1", has "price", "high52", "low52", etc.
The row 'header' "$A3", has the ticker, so "LUK" or JEF etc.

In case you're not aware, the $ means it's absolute, not relative.  So if you fill the cells from that cell, then that value will stay the same on an absolute basis..

wellmont

  • Guest
Re: Google spreadsheet tips and tricks
« Reply #2 on: February 02, 2013, 09:26:21 AM »
thanks. the import worked like a charm.

racemize

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 2740
Re: Google spreadsheet tips and tricks
« Reply #3 on: March 14, 2013, 09:14:52 AM »
I just started importing in options--is there a way to format the cell so that it is in $ format?  Every time I reformat, it just ignores and shows as a number.  The OC in me is upset!

Yours Truly

  • Sr. Member
  • ****
  • Posts: 390
Re: Google spreadsheet tips and tricks
« Reply #4 on: March 14, 2013, 09:59:43 AM »
Does anyone know how I can create a link from one tab to another?

I.E. I want a hyperlink placed on Tab A.. and when clicked, it'll show Tab B afterwards

racemize

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 2740
Re: Google spreadsheet tips and tricks
« Reply #5 on: March 14, 2013, 10:24:43 AM »
Does anyone know how I can create a link from one tab to another?

I.E. I want a hyperlink placed on Tab A.. and when clicked, it'll show Tab B afterwards

I am not sure how to do that, but why wouldn't you just use the tabs at the bottom?

Yours Truly

  • Sr. Member
  • ****
  • Posts: 390
Re: Google spreadsheet tips and tricks
« Reply #6 on: March 14, 2013, 09:56:08 PM »
Does anyone know how I can create a link from one tab to another?

I.E. I want a hyperlink placed on Tab A.. and when clicked, it'll show Tab B afterwards

I am not sure how to do that, but why wouldn't you just use the tabs at the bottom?

Because I have many tabs consisting of each companies financial data... My main page serves as a summary... So we're talking about 150 tabs which is painful to scroll through

Martian

  • Jr. Member
  • **
  • Posts: 63
Re: Google spreadsheet tips and tricks
« Reply #7 on: March 15, 2013, 09:05:27 AM »
I could not find a way to easily link one tab with another..but this is a workaround.

1. When you go to the destination tab, copy the url in the browser (it will have something like #gid=1 in the end)
2. Go to cell where you want the link
3. in the formula enter the following

=HYPERLINK("https://docs.google.com/spreadsheet/ccc?key=0Au85vG0QePvMdDFvWEY0LU1Gsdfsfsdfssddsad#gid=1" , "Link Name")

This will open the tab in the new browser. also if the order changes, I think you need to link again.
« Last Edit: March 15, 2013, 09:07:45 AM by Martian »

MYDemaray

  • Sr. Member
  • ****
  • Posts: 340
Re: Google spreadsheet tips and tricks
« Reply #8 on: March 15, 2013, 10:47:33 AM »
not exactly what you're looking for but I think it will work.  Google spreadsheets supports named cells and ranges.  Select the cell you're interested in linking to.  Go to DATA>Named and protected ranges.  A side bar will appear.  Click the "+" button.  Select and name your cell or range (e.g., "Test").  Then go to any other cell in the workbook and type =Test , and that cell's value should appear.

Update: uh...sorry, totally misunderstood the issue
« Last Edit: March 15, 2013, 08:11:08 PM by MYDemaray »

yitech

  • Full Member
  • ***
  • Posts: 146
Re: Google spreadsheet tips and tricks
« Reply #9 on: March 15, 2013, 09:44:43 PM »
I just started importing in options--is there a way to format the cell so that it is in $ format?  Every time I reformat, it just ignores and shows as a number.  The OC in me is upset!

Simply add a "+0" in the end. It will convert string of numbers to decimal format.