Expanding on Google Finance Spreadsheet functions

Yesterday I wrote about a neat trick of using Google spreadsheets to display the stock prices and today I will expand upon the topic.  While it is nice to know what the stock price is right now it is all relative unless you have a history of how the stock has been priced throughout the year.

I don't know how each one of you learns, but this is something that can be done while you read this post which in my mind is always a plus.

Let's start with a new Google spreadsheet and click on cell A1.  We are going to start with tracking Google and it's performance in 2009.  The function we are going to be using is an expansion of yesterday's:

=GoogleFinance("ticker", "attribute", "start_date", "end_date", "interval")

For us to track Google's stock performance of 2009 we will input the following in cell A1:

=GoogleFinance("GOOG", "all", "1/1/2009", "12/20/2009", "WEEKLY"

Once you press enter there will be a pause as Google retrieves the data from the Google Finance website.  Once the page refreshes you will notice that there is now data in columns A through F.  The one thing to note is that nowhere on the spreadsheet does it say that this information is for Google's stock.  For our tracking purposes we will label the sheet by clicking on the tab labeled sheet 1, selecting rename and entering in Google.

Congratulations you have just performed the first step in looking up, plotting and reviewing the 1 year historical data of Google's stock.  Adding new sheets is as easy as clicking on the add sheet button at the bottom of the page.  For each additional stock you want to track all you have to do is add the sheet, input the formula above and change out GOOG with the stock ticker of your choice.

Again, I hope you learned something and gave this a try.  I will be continuing this theme in the coming days to show you how to create charts that will combine all these steps.  Until then, take care, be safe and have a Merry Christmas.

Neat Google Spreadsheet Tricks

I recently read an article about the ever improving Google Apps and how Google has incorporated search functions into their spreadsheet application.

Avid spreadsheet users have known for years how to use a spreadsheet to track, sort, and analyze numerous types of data but never before has a spreadsheet been tweaked to allow for instant answers to queries submitted to the internet.

Say for example that you are interested in tracking the price of your favorite stock you can enter the following in the formula bar:  =GoogleFinance("GOOG", "Price") and when you hit enter the spreadsheet will perform a search of the Google Finance website and return the price of the stock. 

My suggestion is to label your spreadsheet as follows to track the following five stocks:

Cell A1:  Google
Cell A2:  =GoogleFinance("GOOG", "Price")
Cell B1:  Microsoft
Cell B2:  =GoogleFinance("MSFT", "Price")
Cell C1:  Cisco
Cell C2:  =GoogleFinance("CSCO", "Price")
Cell D1:  VMware Inc
Cell D2:  =GoogleFinance("VMW", "Price")
Cell E1:  Oracle
Cell E2:  =GoogleFinance("ORCL", "Price")

All of these stocks are leaders in the technology sector and I would recommend owning any of them in a long term stock portfolio.  If you are wary of buying individual stocks I would recommend buying shares of the iShares Dow Jones US Technology ETF which has a stock ticker of IYW which would can be tracked in cell F as:

Cell F1:  iShares Dow Jones US Technology ETF
Cell F2:  =GoogleFinance("IYW", "Price")

This spreadsheet will update itself when you open it as it performs a query against the Google Finance website.  If you want to track the history of a stock price then you can manually enter the price in the cell underneath (example would be to put $596.27 in Cell A3.  Now if you do that you would need to enter a date, in which case I would insert a new column to the left of A making it the date column which could then be used as your temporal tracker.  I will write later on how to capture this data to plot the historical price of your stocks.

If you have any questions feel free to post a comment, and if you don't have a gmail account yet, what are you waiting for?