{"id":3397,"date":"2022-04-27T11:47:28","date_gmt":"2022-04-27T11:47:28","guid":{"rendered":"https:\/\/www.paytmmoney.com\/blog\/?p=3397"},"modified":"2022-04-28T06:38:26","modified_gmt":"2022-04-28T06:38:26","slug":"magic-of-portfolio-charts","status":"publish","type":"post","link":"https:\/\/www.paytmmoney.com\/blog\/magic-of-portfolio-charts\/","title":{"rendered":"Magic of Portfolio Charts"},"content":{"rendered":"

An equity market is a market in which shares of companies are issued and traded. It is basically a meeting point for buyers and sellers of stocks. When an investor buys stocks that ultimately get credited in his \/ her Demat account, it forms part of their holdings in\u00a0 his \/ her portfolio. An investor typically needs\u00a0 a system by which, over a period of time, his \/ her portfolio performance could be tracked and also to get some insights. To support this, we have developed the <\/span>Portfolio Charts<\/b> system that would give a graphical representation of an investor’s total purchase value and current market value.<\/span><\/p>\n

In this blog post, we will give an overview of the <\/span>Portfolio Charts<\/b> system at Paytm Money and share some of the learnings we gained along the way.<\/span><\/p>\n

\"Magic<\/p>\n

Background Knowledge<\/b><\/p>\n

First, let\u2019s talk about how the holdings information gets generated based on the stocks bought by an investor. Let\u2019s suppose below are the user\u2019s transactions.<\/span><\/p>\n\n\n\n\n\n\n\n\n
Date<\/b><\/td>\nStock<\/b><\/td>\nType<\/b><\/td>\nQuantity<\/b><\/td>\n<\/tr>\n
03 Jan 2022<\/span><\/td>\nS1<\/span><\/td>\nBuy<\/span><\/td>\n10<\/span><\/td>\n<\/tr>\n
03 Jan 2022<\/span><\/td>\nS2<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n<\/tr>\n
04 Jan 2022<\/span><\/td>\nS1<\/span><\/td>\nSell<\/span><\/td>\n5<\/span><\/td>\n<\/tr>\n
05 Jan 2022<\/span><\/td>\nS3<\/span><\/td>\nBuy<\/span><\/td>\n10<\/span><\/td>\n<\/tr>\n
05 Jan 2022<\/span><\/td>\nS2<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

So at the end of 05 Jan 2022 and onwards, we will have four records as the buy transactions get accumulated, if not sold.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Holding Date<\/b><\/td>\nStock<\/b><\/td>\nType<\/b><\/td>\nQuantity<\/b><\/td>\nBuy Date<\/b><\/td>\n<\/tr>\n
03 Jan 2022<\/span><\/td>\nS1<\/span><\/td>\nBuy<\/span><\/td>\n10<\/span><\/td>\n03 Jan 2022<\/span><\/td>\n<\/tr>\n
S2<\/span><\/td>\nBuy\u00a0<\/span><\/td>\n5<\/span><\/td>\n03 Jan 2022<\/span><\/td>\n<\/tr>\n
04 Jan 2022<\/span><\/td>\nS1<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n03 Jan 2022<\/span><\/td>\n<\/tr>\n
S2<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n03 Jan 2022<\/span><\/td>\n<\/tr>\n
05 Jan 2022<\/span><\/td>\nS1<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n03 Jan 2022<\/span><\/td>\n<\/tr>\n
S2<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n03 Jan 2022<\/span><\/td>\n<\/tr>\n
S2<\/span><\/td>\nBuy<\/span><\/td>\n5<\/span><\/td>\n05 Jan 2022<\/span><\/td>\n<\/tr>\n
S3<\/span><\/td>\nBuy<\/span><\/td>\n10<\/span><\/td>\n05 Jan 2022<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The idea of the above table is to let you imagine how the records will grow over a period of time.<\/span><\/p>\n

Problem Statement<\/b><\/p>\n

In Paytm Money, we have a third party legacy system which generates the users\u2019 holdings based on transactions on a daily basis and uses an MS SQL Server at the persistence layer. Database tables which contain the holdings have more than 25 columns and billions of rows as holdings get accumulated. They store only the last N(15) days\u2019 holdings because the latest date\u2019s record is sufficient to know a user\u2019s holdings and the last N days\u2019 holdings are good enough to correct the system in case any inconsistencies are found.\u00a0 This would also reduce the amount of storage used. Apart from this, the third party system suffered from the following problems –\u00a0<\/span><\/p>\n