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.

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

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.

May 5, 2019 at 12:07 PM

I will not mind if you don't say thank you to me in future as I know that you will be of course a happy person that you know that you can earn a big amount of dollars in just a week or in just a month. Lucrotrade

September 3, 2019 at 11:17 PM

I really appreciate! I am So glad the article was very useful for me! Thank you for the great post. silk bank loan calculator - bank alfalah car ijarah calculator

December 1, 2019 at 2:04 PM

I strongly recommend Dumb Bitch Trades. This trading platform combines the real time data and the advance tools and functions to deliver the most accurate, unparalleled market trading experience. best books to learn stock trading

June 25, 2020 at 3:48 AM

Thanks for sharing the information. That’s a awesome article you posted. I found the post very useful as well as interesting. I will come back to read some more sensex

November 7, 2021 at 11:28 AM

Whether you’re looking for a full-service virtual accounting firm or a basic online bookkeeping service with free software, there’s likely a provider that will suit your needs. Charles Stewart Accountants

February 4, 2022 at 8:49 PM

I have been searching for such an informative post for many days and it seems my search just ended here. good work. keep posting. If you want to get zulutrade combos. for more info visit our website.

Post a Comment