How to Add Google Finance Functions to Your Google Spreadsheet

One of the most important aspects of swing 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 swing trading log:

image

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

image
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 )



image

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



image

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.

17 Response to How to Add Google Finance Functions to Your Google Spreadsheet

June 21, 2013 at 8:34 AM

Help, help, help please. I have spent an hour trying to find a list of valid googlefinance parameters.
I can't believe that they created a program and then left it up to users to guess how it worked - but I can't find a list of parameters anywhere.
I see that you are aware of at least some that work in the function.
Can you point me to anyplace that actually documents the googlefinance function AND INCLUDES A LIST OF VALID PARAMETERS.
My general opinion of Google has gone down dramatically in the past hour. This is (ridiculous, incompetent, stupid) (pick one).
Thanks for any help.

June 21, 2013 at 12:44 PM

Here are a few more examples of attributes you can access using the GoogleFinance function:
price: market price of the stock.
priceopen: the opening price of the stock for the current day.
high: the highest price the stock traded for the current day.
low: the lowest price the stock traded for the current day.
volume: number of shares traded of this stock for the current day.
marketcap: the market cap of the stock.
tradetime: the last time the stock traded.
datadelay: the delay in the data presented for this stock using the googleFinance() function.
volumeavg: the average volume for this stock.
pe: the Price-to-Earnings ratio for this stock.
eps: the earnings-per-share for this stock.
high52: the 52-week high for this stock.
low52: the 52-week low for this stock.
change: the change in the price of this stock since yesterday's market close.
beta: the beta value of this stock.
changepct: the percentage change in the price of this stock since yesterday's close.
closeyest: yesterday's closing price of this stock.
shares: the number of shares outstanding of this stock.
currency: the currency in which this stock is traded.

June 21, 2013 at 12:45 PM

Looks like the original reference was moved. I found this one that covers most of the functions:

http://googledocs.blogspot.com/2010/08/tips-tricks-googlefinance-in-google.html

July 15, 2013 at 9:56 AM

Your sample spreadsheet does not use the function(s):

=INDEX(GoogleFinance(b2 ; ʺpriceʺ ; ʺ3/25/2013ʺ) ; 2 ; 2 )

To get the historical stock price. I have tried to use it but it does not work the way you explain it. Any thoughts on this and how you can get it to work as you describe?

July 15, 2013 at 10:20 AM

OK, I take back my comment about it not working. It does work. I'm just not sure why you didn't use the function in your example spreadsheet.

July 15, 2013 at 10:48 AM

The function in the spreadsheet gets current price. The example above is used to get a historical price.

I've added a column using the example above.

thanks.

July 21, 2014 at 2:53 AM

The first reason to use Zulu4me’s ranking is because it is freeSocial trading Forex. You do not have to pay to use our ranking,Forex signals free you just need to create a Demo Account in Zulutrade through us.Forex signals review This way, you do not need to study more than 20.000 traders,Forex signal provider you just need to study 20 or 30 traders. Finally, if even knowing that a personal assistant will help you,Forex signal service you are still not sure about Zulu4me advantages,Trading systems that workyou should continue reading this post.

October 1, 2014 at 2:18 AM

I have to thank you for this good site .I for sure loved every little bit of it. I have you bookmarked your web site to look at the latest stuff you put up.

GOOG Stock Quote

October 26, 2015 at 5:15 AM

A financial adviser (or advisor) is a professional who renders financial services to clients. According to the U.S. Financial Industry Regulatory Authority (FINRA), terms such as financial adviser and financial planner are general terms or job titles used by investment professionals. financial advisor columbia sc

November 2, 2015 at 4:18 AM

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. buy ukash online with paypal

December 22, 2015 at 5:03 AM

Google Security Team acknowledged me that someone else reported this issue before i have.financial advisor lexington sc

February 13, 2016 at 4:57 AM

a

February 15, 2016 at 6:45 AM

This is a cool post but there is a good trading robot available which you can learn about and it could make some of your project simpler.

February 16, 2016 at 1:02 AM

This is really helpful, thank you for sharing this information. If you want info on traders, I would really recommend having a look at Elite Forex Trading, who really helped me.

February 18, 2016 at 3:32 AM

Thanks for writing this blog. This is a good article about how to deal with finances in a spreadsheet and I think that you will like to read this Thomas

April 28, 2018 at 7:25 AM

Thanks for these great tips. Once you begin investing in stock, you need to pay close attention to the market capitalization of each stock you own.

July 20, 2018 at 4:16 AM


"Which Forex pair and time frame is best to trade" is the frequently asked question and I want do give you the DEFINITE ANSWER.

Are you expecting that I am going to say something like EUR/USD on 5-minute time frame or GBP/USD on daily...? No, it is not so simple, but SIMPLE ENOUGH we can figure it out!

The "PROBLEM" is that markets change over time. If GBP/USD was a well trending currency pair a few years ago, today it is another one.

I actually want to let you know about a SPECIAL TOOL that I use to find the BEST TRENDING PAIRS among all the Forex pairs.

http://www.forextrendy.com?vhbshygdf398432

Post a Comment