Google spreadsheet tips and tricks

[bargainman]

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:

 

 

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]

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..

[Guest wellmont]

thanks. the import worked like a charm.

[racemize]

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]

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]

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]

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]

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.

 

[MYDemaray]

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

[yitech]

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.

[racemize]

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.

 

Thanks, my inner OCD is very appreciative.

[lessthaniv]

is there a way to track dividend yields?

[frog03]

The spreadsheet does not like some markets.  I can't seem to get any prices for Swiss stocks.

[bargainman]

is there a way to track dividend yields?

 

 

Take a look at the first message in the thread.  The ROIC example shows how to do it.  You need to get the information from Yahoo since Google finance doesn't give yield for stocks (they do for Mutual funds for some odd reason..)

[racemize]

So, S&P changed their website, so I can't pull the TR from them anymore. I've come up with a work around:

 

This gives you last day's TR:

=(Index(ImportHtml("http://quicktake.morningstar.com/index/IndexCharts.aspx?Symbol=SPX", "table", 10),3,7))/100

 

and this will add yesterday's TR and the current change in S&P (though I'm not sure what will happen at night when the other updates, yet):

={CELL FOR TR}+GoogleFinance(".INX","changepct")/100

 

I suspect this will end up breaking at night (when the changepct shouldn't be added, or perhaps it should be added, but it has to be listed as "0").  I'll find out and figure it out tonight, likely.

[augustabound]

So, S&P changed their website, so I can't pull the TR from them anymore. I've come up with a work around:

 

This gives you last day's TR:

=(Index(ImportHtml("http://quicktake.morningstar.com/index/IndexCharts.aspx?Symbol=SPX", "table", 10),3,7))/100

 

and this will add yesterday's TR and the current change in S&P (though I'm not sure what will happen at night when the other updates, yet):

={CELL FOR TR}+GoogleFinance(".INX","changepct")/100

 

I suspect this will end up breaking at night (when the changepct shouldn't be added, or perhaps it should be added, but it has to be listed as "0").  I'll find out and figure it out tonight, likely.

 

I'm pulling data into my spreadsheets but I'm not sure how to reference the ticker in one instance.  If I have multiple tickers in column A, all my data is pulled in the remaining columns, price, p/e etc.  But how can I reference the ticker in column A within column F automatically?

 

Here's my example since I know I'm not explaining it well enough.  ;D

 

If I want to pull in dividend growth from gurufocus since they do this for 10 years, how do I make the cell reference the ticker in column A?

 

http://www.gurufocus.com/dividend/csco

It pulls the dividend page, 3rd table, row 2 properly but I need this for multiple tickers so the data is pulled automatically when I input a ticker in column A.

 

This works for Cisco =Index(ImportHtml("http://www.gurufocus.com/dividend/csco","table",3),2)

What do I replace CSCO with?

[writser]

ImportHtml(http://www.gurufocus.com/dividend/" & A1,"table",3),2)

 

This should do the trick, where A1 is your cell.

[augustabound]

ImportHtml(http://www.gurufocus.com/dividend/" & A1,"table",3),2)

 

This should do the trick, where A1 is your cell.

 

You're the best.  :)

I had tried the & A1 at one point but it didn't work, not sure why but that doesn't matter now.

I may have misplaced the end quote after the url come to think of it.

 

Thank you

[augustabound]

This post from Gamecock made me think of another question.

If anything I'm an excel wizard now because of it. You'l never click and drag again.

Using my previous cell example;

=Index(ImportHtml("http://www.gurufocus.com/dividend/" & A3,"table",3),2)

 

If I have tickers in column A and this dividend example is in cell B1 and I'm starting a new sheet, how can I use the array function with the example so the reference to gurufocus will automatically populate all cells in column B?

I've tried the array command with another set of parenthesis around the existing command but it didn't work.

 

Clicking the cell and dragging the blue box down is a pain since I have a dozen or so in row 1 I want populated down each column. I may add more since I'm just messing around with portfolio and watchlist ideas for now.

 

Still sitting at home nursing my achilles isn't good for my organization OCD.  ;D

[augustabound]

In case anyone else needs this I found the answer for both Excel and Google spreadsheets.

 

Google spreadsheets

Highlight the row your formulas are in (in my case row 2). Ctrl, shift and hold the down arrow until the sheet is highlighted. 

Control D populates the cells with each formula for every column.

This is for the entire sheet when multiple columns have formulas, I haven't tried just 1 column at a time.

 

Excel

Highlight the column, type in your formula and hold down Ctrl while you press Enter.

 

Now I can sleep tonight.    ;D

 

Edit: Apparently in Excel you can highlight the column and click "Fill" from the ribbon too. 

[jay21]

Anyone use google sheets for tracking sports stats?  If so, I'd love to know how.

 

Thanks

[jay21]

I thought someone told me you can use google sheets to import the Yahoo News feed for certain ticker symbols. Anyone know how to do this?

[Fat Pitch]

Does anyone know how to pull the Financials from Morningstar into Googlespread sheet? For example I'm trying to pull the 10yr financial summaries from this link http://financials.morningstar.com/ratios/r.html?t=MSFT&region=usa&culture=en-US

 

I played around with every table/row/column combination, but the table won't pull through.

[bargainman]

Does anyone know how to pull the Financials from Morningstar into Googlespread sheet? For example I'm trying to pull the 10yr financial summaries from this link http://financials.morningstar.com/ratios/r.html?t=MSFT&region=usa&culture=en-US

 

I played around with every table/row/column combination, but the table won't pull through.

 

The data is probably pulled after the pages actually loaded into the browser using some sort of script. In this case it will probably be pretty tough to get it into Google spreadsheet.  For example this is actual URL worth of data is downloaded

 

http://financials.morningstar.com/financials/getFinancePart.html?&callback=jQuery19107273107280489057_1415250879327&t=XNAS:MSFT&region=usa&culture=en-US&cur=&order=asc&_=1415250879328

 

I was able to get the JSON this way:

http://financials.morningstar.com/financials/getFinancePart.html?&t=XNAS:MSFT&region=usa&culture=en-US&cur=&order=asc

 

 

Good luck

 

[Vish_ram]

Get the google script ImportJSON from https://github.com/fastfedora/google-docs/blob/master/scripts/ImportJSON/Code.gs

 

then in google spreadsheet, use this script and give the URL that fetches the JSON as a parameter. I used this to show data in econtracker.com