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.
You need a Google account to use this. Visit [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")
Thanks so much for portolio and watchlist spreadsheet. It’s very useful and easy to use. I’m getting a different perspective on my portolio and I’m sure that any somewhat knowledgeable investor would take one look at my portfolio and identify me as a value investor, based on you look-through metrics! That’s good, because that’s how I identify myself, and more importantly I’m acting like a value investor (albeit a mechanical one, via the Magic Formula)
I’m not sure how to include other financial data so that we can add look-through on other aspects such as (as you mentioned) cash flow, free cash flow, or debt-t0-equity.
I hope you’d be willing to include those measures in the future.
Great spreadsheet. Thanks so much!
Hi Greg,
Thank you for the kind words.
I too search for stocks by using the Magic Formula screener. I think its a great way to look for ideas.
For the other financial data, one has to do some extra work. Since Google Finance currently does not return those metrics automatically. One way to do it could be to create a seperate sheet with multiple columns that hosts data such as cash flow from operations, FCF, LT debt, shareholder equity. Then, in the Portfolio sheet, you could insert columns that would compute P/FCF, P/CF, Debt / Equity. This would be done for each row. Next thing would be to multiple the weight column by each of those measures ( similar to what I have done for market cap). The reason for this is obvious, the weights of each stock in the portfolio is different.
Finally, to arrive at the metric at the portfolio level, sum all the rows for the column.
I think the sheet will become quite complex with the addition of so many columns. Maybe it may make sense to split the portfolio sheet into 3 sheets. One that tracks the portfolio performance, one that has all the portfolio and stock metrics, and one that contains the data to be used for calculations for the metric sheet.
See if you can try to do this for one metric and let me know how it goes.
Good luck.
Pingback: Search strategy | Adib Motiwala's Blog