4 Replies Latest reply on Nov 3, 2018 7:36 AM by Abhishek Agarwal

    Calculate & Visualize Portfolio Returns using single stock prices/returns

    Abhishek Agarwal

      Hello Everyone,

      I am stuck with this problem for a while and would really appreciate if someone could help me resolve it. I have a simple spreadsheet containing Date, Portfolio Name, Symbol, Quantity, price, sector, daily return, notional.

       

      I am trying to create a performance dashboard where I can select individual or combination of portfolios (by their name) and be able to see their cumulative returns from a user defined reference date. So for e.g. if a user selects portfolio ABC in the attached dashboard, he should be able to see the the time series of the return as - weight of stock A*return on stock A +weight of stock B*return on stock B +..... etc. Similarly he can select any combination of the portfolio to visualize performance from a user defined reference date.

       

      I created a basic visualization but this is not working. The cumulative return in my formula is just returning the percentage change of prices of the stock on a particular day - sum of price of all stocks on reference date, which is wrong. It should be the sum of (All the percentage change of individual stocks in the portfolio * their individual weight on the reference date).

       

      I am attaching the workbook for reference. I would really appreciate if someone could help me with it. The workbook attached is in version 10.2