Creating historical stock returns chart using Numbers on Mac

Numbers on Mac provides two very useful functions, STOCK and STOCKH, which return stock price using Yahoo! Finance. You can use STOCKH to fetch historical stock price for a stock, and create chart for tracking historical movements of your investment. All without writing a single line of code. For e.g. here I created historical chart for my investment in Greenply. It’s nothing to brag about but–as you can see–it details out the price movements of my principal and investment value over 3 years.

You can download the Numbers Spreadsheet from below.

You can go ahead and customise it for your investments.

Below are some key details that you will need to take care of.

  • In the cells E1, F1…, enter the code of stock as per Yahoo! Finance. Greenply demerged its MDF business in Greenpanel in the ratio 1:1 giving me additional 41 shares for zero cost. So you see that in the cell G1.
  • In the cells E2, F2…, enter the date of purchase.
  • In the cells E3, F3…, enter the quantity of purchase.
  • In the cell A6, enter date of first purchase of stock. The subsequent cells will increment with 1 day interval. Feel free to customise the interval to 1 week as it might become difficult to maintain after 5 years.
  • The chart gets generated based on the values in the Date (A5), Principal Value (B5) and Investment Value (C5).