* Reference: YouTube - MyOnlineTrainingHub dated Feb. 3, 2021 ...
https://www.youtube.com/watch?v=D44EDant0rs
... Good day ... What additional Excel Formula to use for =SORT(FILTER( ... )) or what will be the best approach if I have a certain Stock Holdings that have additional Purchased Share Units but with different Share Price and it is not reflected correctly in the sample dashboard attached ? Appreciate your kind advise. Thank you in advance.
By the way, Mynda, thank you for sharing the Stock Dashboard that you had created. It was a great help to give me an idea to create my own dashboard. However, the main Dashboard discussed in the said YouTube video was a consolidated stocks regardless of the purchased price differences. So I tried to modify the given formula by removing the UNIQUE function. However, as shown from the attached file, the price and number of units reflected was not correct with reference to the sample transactions in the ledger. It only captures the first scanned values. My intention is to also monitor the Gain/Loss of the number of units purchased based on the acquired Price per share.
Hope you can help or someone from the community who might probably done similar purpose. Thank you again in advance. Have a great day ahead.
Hi Mac,
If you want to track separate purchases and sales of the same stock, you'd need to add a column to your ledger that tags the stock purchase with a unique identifier per stock so they can be identified into tranches. e.g. add a column called Tranche and then number the purchases e.g.
Date Stock Tranche
01-03-2024 (XNAS:MSFT) 1
08-03-2024 (XNAS:MSFT) 2
04-03-2024 (XNAS:TSLA) 1
You can also use the Tranche column to group/match purchases and sales within a tranche to get the correct price.
Mynda
Hi Mynda,
Good day and thank you for the response.
Your recommendation is noted and had applied the additional "Tranche" column to my ledger (attached revised Excel file).
However, there were no changes or impact to the draft Dashboard. The "Paid Units" and "initial Price / Share" shown in the Dashboard where still the first scanned values.
How will the additional "Tranche" column be defined in the concerned formulas to show the correct "Paid Units" and "initial Price / Share" according to the sorted Stocks and defined "Tranche" column.
Appreciate your kind advise.
Have a great and safe day ahead.
Best regards,
Mac
Hi Mynda,
For update, I tried to further isolate the issue/concern and was able to find a work around solution without using the additional "Tranche" column but not sure if it will be effective as I added new set of Stocks to the Ledger and Dashboard moving forward. (attached revised file)
The disadvantage for the work around done is that the following columns are not dynamic anymore as I add more stocks to monitor, manual work will be done
* Paid Units
* Market Value
* Cost Price (initial price / share)
* Gain / (Loss)
Below is the work around formulas that seems to be working ...
=INDEX(MyLedger[Units],IF($C6=$C5,MATCH($C6,MyLedger[Stocks],0)+1,MATCH($C6,MyLedger[Stocks],0)))
=INDEX(MyLedger[Cost Price],IF($C6=$C5,MATCH($C6,MyLedger[Stocks],0)+1,MATCH($C6,MyLedger[Stocks],0)))
Appreciate your kind advise if you have alternatives to the said formulas that can be dynamic accordingly as new stocks will be added.
Have a great and safe day.
Best regards,
Mac
Hi Mac,
I don't see how this will work when you sell some of the stocks and have those sale entries in your ledger. I thought you'd want to reduce the original purchase of stocks to reflect the new balance after the sale.
I think the problem is the sheet you call MyLedger isn't a ledger. If you look at the ledger in my example, you'll see it's a list of the buy and sell transactions. i.e. the actual money I spent to buy the stocks or made when I sold the stocks. You have skipped this step, so essentially your ledger and dashboard are the same thing.
The dashboard should be a summary of your position made up of the transactions in your ledger. The Tranche column will enable you to match purchase and sale transactions of the same stock to one another so you can get an accurate view of your profit/loss when you sell a stock.
Hope that points you in the right direction.
Mynda
Hi Mynda,
Good day and hope you're doing well.
Thank you again for the response, clarification and insights to this matter, it is acknowledged / noted and appreciated it well.
I'll revise my Stock Portfolio Dashboard again to reflect back the original formulas and format you had discussed from your previous YouTube, after realizing your insights are correct.
Again, thank you very much for your assistance to this matter.
Have a great and safe weekend ahead.
Best regards,
Mac
