Friday, May 10, 2013

Google Finance Function for Stocks

I saw this at

and wanted to archive it for myself


How to Add Google Finance Functions to Your Google Spreadsheet

One of the most important aspects of trading is keeping a log of your trades. The amount of work involved with tracking data can be overwhelming. Google Spreadsheet is a great solution since you can access it from any where. The interface is easy to use and you can embed Google Finance functions. These functions will grab today’s stock data or historical data. Let’s cover the basics on building out a trading log:


First we want to add the headers to the document: Date, Symbol, Purchase Price, Current Price, Change, Total Shares, and Gain/Loss.

After you have added your headers, Freeze row 1 by going to the View Menu and click “Freeze Rows: Freeze 1 row” from the drop down menu. This prevents your headers from moving off the screen when your log begins to grow.

Now with the headers set, it’s time to review a few of the basic Google Finance Functions.

To get the Current Price of any stock: =GoogleFinance(B2 ; ʺpriceʺ)
Just like excel you can reference the Cell that contains the ticker symbol. In this Case B2 which is “CSU”.

To get the Open Price of any stock: =GoogleFinance(B2 ; ʺpriceopenʺ)
To get today’s volume: =GoogleFinance(B2; "volume")
To get today’s high: =GoogleFinance(B2; "high")
To get today’s low: =GoogleFinance(B2; "low")
To get a historical price use: =INDEX(GoogleFinance(b2 ; ʺpriceʺ ; ʺ3/25/2013ʺ) ; 2 ; 2 )


Once you’ve inserted the Google Finance functions you can use a few basic formulas to calculate Change and Gain/Loss.
To calculate total change: =D2-C2
Total calculate Gain/Loss: =E3 * D3


Google spreadsheet is easy to use and the integration with Google Finance is a big plus. You can access your spreadsheet from any device across the internet. If you have not started that trading log, now is the time. Below is an example to help get you started.
Example Spreadsheet
You can find the complete list of Google Functions here.

No comments:

Post a Comment

Insightful and Useful Comment!