For full text search please use the '?' prefix. e.g. ? Onboarding

Measure Stock Trading Performance With Google Sheets

I want to create a spreadsheet to monitor the performance of trading stocks in Vietnam market.

Deliverables

  • Click here to access my sample spreadsheet, with sample data and user-defined functions included. The custom functions only work with the data structure presented in the sample spreadsheet.
  • Or you can also review separately the code of user-defined functions at this gist.

Project status

DONE

  • Input manually each trade into orderBook sheet
  • View recap of the portfolio with the automated sheet pnl, with the current value of the portfolio, realized/unrealized P/L
  • View list of executed trades, sorted by ticker in the automated sheet orderBook_sortedByTicker
  • Compute cost basis of stocks with FIFO method
  • refactor the formula to calculate the average cost per share (take into account only the unsold shares in portfolio)

Thoughts

From the article of Tools to research Vietnam stock market, I learned that there is no free API data feed for hobbyist to play with the end-of-day historical price data of Vietnam' securities. So I intend to crawl data from investing.com using investiny package then import into Google Sheets.

2022-12-09 update: TIL

  • I can simply use the IMPORTXML formula to import the historical data from investing.com into Google Sheets. Click here to read the tutorial written by coupler.io. For this reason, the plan to practice python for web scraping is procrastinated again
  • IMPORTXML refreshed automatically only if the document is opened, which is good enough for current usage 1
  • Apply Custom Formatting Numbers to indicate the millions by M, the thousands with a k.

2022-12-23 update: TIL

  • need to refactor formula in pnl sheet since orderBook now has 3 types of transaction (buy, sell, stock dividend)
  • add a new column for income tax on selling securities. Learn the calculation at Tax Basics for Investors in Vietnam
  • Calculate the column rate of return (unrealized) (weighted by cost of purchasing)
    • use SUMPRODUCT combined with ABS formula to get the sum of absolute value of a mixture of positive and negative numbers
    • SUMPRODUCT(ABS($E$2:$E))
    • in SUMPRODUCT formula, when array2 is omitted as above, the formula will calculate the sum of the products of the 2 arrays: array1 and {1,1,1,...} with same length as array1.
    • Read more about How To Get Absolute Value In Google Sheets

2022-12-24 update:

  • refactor the formula to calculate Realized Gain/Loss, applying the accounting FIFO (first in, first out). It means that the shares I bought earliest will be the shares I sell first. Read more at here
  • add a dedicated note explaining how did I record transaction into orderBook.

2023-01-03 update:

  • Add custom script
    • to automatically record a daily history of values in pnl sheet
    • to force refreshing IMPORTXML function in tmp_currentPrice sheet, while the document is not opened
  • Reason: watch the daily performance of my portfolio, to learn when I missed the chance to realize the profit.

2023-01-06 update:

2023-01-16 update:

  • Refactor the custom script to automatically record a daily history of values in pnl sheet
  • Reason: enhance the performance of the batch operations.

2023-01-17 update:

  • Refactor the custom script to automatically record a daily history of values in pnl sheet
  • Reason: replace the configuration of installable trigger from UI to apps script
Children
  1. Average Cost per Share
  2. Cost basis with FIFO method
  3. Data structure
  4. Track Daily History

Tags

  1. cat.tut
  2. topic.investment

Footnotes

  1. https://support.google.com/docs/answer/12188454?hl=en˄