My wife and I are getting ready for a little friendly stock competition where we’re both starting with a small amount of money and seeing what we can do with it playing the stock market. I thought it would be nice to share with readers how the stocks we’re buying and selling are doing.

There’s already a program running on the server (pictured right) that I wrote which grabs the stock price for the tech stocks I own and charts the progress throughout trading day (see the finance category on stock days). It saves all the data in a relational database so I can run additional queries whenever I want. It works good for stocks I’m holding and want to chart over a long period of time but it isn’t as easily updated for more active buying and selling. I decided to try using Google Spreadsheets instead (Disclaimer: I own Google stock) because:

- my wife and I can collaborate on the spreadsheet
- it will be easier for my wife to update from her browser any time she wants
- changes to the spreadsheet can automatically updated on the blog
- it will give me a chance to work with the Google Spreadsheet API. This will allow me to be able to include parts of the shared spreadsheet at Hmm (programming required)

The spreadsheet design
Let’s go through the process step-by-step of creating the spreadsheet and then setting so it is a shared spreadsheet that anybody can view.

STEP 1. Login to docs.google.com and select New->Spreadsheet

STEP 2. Give your spreadsheet a name by clicking on the “Untitled” heading

STEP 3. Add titles to columns. In our case there will be two groups, one for me, one for my wife with the following eight column names: stock symbol, current price, closing price yesterday, price change since yesterday, buy price (which we will fill out when we buy the stock), number of shares, sell price (we will filll this out when we sell) and profit/loss which will be calculated in the spreadsheet. I color coded the cells cyan for me and violet for my wife by clicking on the edit background color cube. Here’s how it looks:

STEP 4. Add GoogleFinance function to price, closeyest and change. There is a built-in Google Spreadsheets function called GoogleFinance which will automatically fill a cell with data taken from Google Finance. The format is:

=GoogleFinance(”symbol or cell”,”attribute”)

Where attribute can be one of the following: price, priceopen, high, low, volume, marketcap, tradetime, datedelay, volumeavg, pe, eps, high52, low52, change, changepct, closeyest, shares, currency.

For details on what each of these attributes does, see this page which also provides attributes for mutual funds tracked on Google Finance.

1) In cell A3, add the stock symbol GOOG for Google (as a test).
2) In cell B3 add the following function:

=GoogleFinance(A3,”price”)

3) In cell C3 add the following function:

=GoogleFinance(A3,”closeyest”)

4) In cell D3 add the following function:

=GoogleFinance(A3,”change”)

This should load the correct values into the spreadsheet. You can have a maximum of 250 GoogleFinance function calls in your spreadsheet. In our case, I right clicked on the computed value and copied into Kara’s cells: J3, K3 and L3 so I didn’t have to retype. I used the stock symbol MSFT for her example. Here’s how it the spreadsheet looks when finished.

Whenever my wife or I buy a new stock during the competition, we add the symbol to the spreadsheet and copy and paste the three function cells mentioned above as well as the buy price and number of shares. If we sell a stock, we add the sell price. The only function missing from the spreadsheet is the profit/loss which we will add in the next step.

STEP 5. Add zeros to the buy price, # shares and sell price and then add the profit/loss function as follows:

=(G3*F3)-(E3*F3)

This will multiply the #shares (F3) times the sell price per share and subtract from the buy share price. If the sales price is higher, there will be a profit, if the sales price is lower then there will be a loss. Without the zeros you’ll get a function error. Once you add real data in those fields the proper calculation will automatically be made by the spreadsheet.

You can play around with the buy/share and #share values and do some hypothetical what if you’d bought 100 shares of Google at $100 a share and see the amount of profit/loss if you sold today.

STEP 6 (Optional). Edit the cell format to be dollars. By choosing “Edit” and then “Format” you can change the format of the cells where money is involved to automatically have dollars and cents. Here’s how that looks:

STEP 7. Time to share the spreadsheet so anybody can view it and add any collaborators. Click on the “Share” tab and check the box in the Advanced Options that says: “anybody can view at…” and then you’ll see a URL for the spreadsheet. Uncheck the other boxes Collaborators can invite other users and invitations may be used by anyone. I added my wife as a collaborator so she can add buy/sell stock information during our competition.

The URL for our competition spreadsheet can be viewed here:

http://spreadsheets.google.com/ccc?key=p0sET08QrXb6U8pxWPRtELA&hl=en_US

We’ll be starting this competition soon.

STEP 8 and beyond. I’ll save the Google Spreadsheet API stuff for another day because I’m learning too, but some ideas for expansion include adding a snapshot of the data to upcoming posts about our stock competition, graphs, historical analysis and more.

Related Posts

• 17 new Google Spreadsheets features
• Share your Zecco trading activity with Zeccoshare now in beta
• GOOG on the S&P 500 after March 31
• Yahoo Finance Market Tracker Real-Time Quotes and Screener 2.0.9
• Couldn’t buy Xbox 360, could buy more GOOGle stock
• How much should your blog / website charge for advertising?