Google Docs has made it very easy to track your portfolio and stocks online. They provide a simple way to retrieve the latest stock price, P/E ratio, Market cap and other attributes (listed below)
All you need is a Google account to use this. Visit http://docs.google.com and create a new spreadsheet.
Lets look at one simple example
To insert the current price of Google stock:
Alternatively, the stock symbol and/or the attribute values can come from spreadsheet cells. For example, the function can be:
In this case, the attribute specified as a string in cell B1 would be returned for the stock symbol in cell A2.
The following types of real-time market data are currently available:
[url=https://spreadsheets.google.com/pub?key=0Ak-RR0F3CKUydEpudzVaWV9OVUc4VUVTQU5DZ3NTTWc&output=html]
Access the spreadsheet here. [/url] Scroll down to the bottom of the page. Click on 'Edit this page'. Then Click on File -> Make a copy. Please feel free to make a copy of it for your own purposes.
Setting up the basic watch list.
1) All you need to do is key in the stock symbol, target buy price (and target sell price).
2) The sheet retrieves the current price, 52 week low, 52 week high, P/E and market cap
3) The sheet then computes various additional values for analysis such as discount to target buy price, % below sell price, upside to target sell price, % above 52 week low, % below 52 week high, earnings yield, P/E at target sell price assuming current EPS, market cap at target sell price.
The discount to target buy price, % above 52 week low and the P/E cells are most useful. They quickly tell you if a stock is trading close to your buy price, or near 52 week low or at a low valuation based on P/E.
Advanced: Using rules to spot things easily.
Color coding the cells can make your life easier. Google Docs has the ability to set the colors of the cells based on some rules. To set a rule, simply right click on a cell and click 'Change colors with Rules'
1) I have set a simple rule to color the 'P/E' cell as green when the cell value is less than 10. You can change it to 8 or 12 as per your preference of low P/E.
2) Similarly, when the current price < target buy price, the discount to target buy price is positive and turns green. If the discount is less than -5%, it turns yellow.
Example: ARO is trading below where I wanted to buy it. It is available at a 14% discount to my target buy price. Also, note that the P/E ratio is below 10 and is also green.
3) If a stock is less than 5% above the 52 week low, I mark the cell as green. This is another typical value search.
Dividend yield is one of the key things that is missing currently. Hopefully the developer at Google docs add that to the list of parameters that can be retrieved.
I hope you all found this post useful. I use this sheet almost daily. I just open it up to see if something has become attractive based on some basic criteria. In my next post, I will present my Google spreadsheet to track a portfolio and use the look through portfolio concept made famous by Warren Buffett.
All you need is a Google account to use this. Visit http://docs.google.com and create a new spreadsheet.
Lets look at one simple example
To insert the current price of Google stock:
=GoogleFinance("GOOG", "price")Alternatively, the stock symbol and/or the attribute values can come from spreadsheet cells. For example, the function can be:
=GoogleFinance(A2, B1)In this case, the attribute specified as a string in cell B1 would be returned for the stock symbol in cell A2.
The following types of real-time market data are currently available:
- price: market price of the stock - delayed by up to 20 minutes.
- 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.
[url=https://spreadsheets.google.com/pub?key=0Ak-RR0F3CKUydEpudzVaWV9OVUc4VUVTQU5DZ3NTTWc&output=html]
Access the spreadsheet here. [/url] Scroll down to the bottom of the page. Click on 'Edit this page'. Then Click on File -> Make a copy. Please feel free to make a copy of it for your own purposes.
Setting up the basic watch list.
1) All you need to do is key in the stock symbol, target buy price (and target sell price).
2) The sheet retrieves the current price, 52 week low, 52 week high, P/E and market cap
3) The sheet then computes various additional values for analysis such as discount to target buy price, % below sell price, upside to target sell price, % above 52 week low, % below 52 week high, earnings yield, P/E at target sell price assuming current EPS, market cap at target sell price.
The discount to target buy price, % above 52 week low and the P/E cells are most useful. They quickly tell you if a stock is trading close to your buy price, or near 52 week low or at a low valuation based on P/E.
Advanced: Using rules to spot things easily.
Color coding the cells can make your life easier. Google Docs has the ability to set the colors of the cells based on some rules. To set a rule, simply right click on a cell and click 'Change colors with Rules'
1) I have set a simple rule to color the 'P/E' cell as green when the cell value is less than 10. You can change it to 8 or 12 as per your preference of low P/E.
2) Similarly, when the current price < target buy price, the discount to target buy price is positive and turns green. If the discount is less than -5%, it turns yellow.
Example: ARO is trading below where I wanted to buy it. It is available at a 14% discount to my target buy price. Also, note that the P/E ratio is below 10 and is also green.
3) If a stock is less than 5% above the 52 week low, I mark the cell as green. This is another typical value search.
Dividend yield is one of the key things that is missing currently. Hopefully the developer at Google docs add that to the list of parameters that can be retrieved.
I hope you all found this post useful. I use this sheet almost daily. I just open it up to see if something has become attractive based on some basic criteria. In my next post, I will present my Google spreadsheet to track a portfolio and use the look through portfolio concept made famous by Warren Buffett.
