Good afternoon board!
I am constructing a portfolio and I want to auto generate my positions. My goal is to determine my portfolio position at every trade date.
Context: I have two tables in a "Transaction History" worksheet, one with all the Trades and the other with all Dividends Received. In a separate "Portfolio" worksheet, I want to arrange every date where a transaction occurred, whether it was a trade or a dividend payment. The rest of that table auto-populates the change in cash and stock position based on the date.
The issue I'm having is to create a formula that will search both tables; Trades and Dividends, which are vertical, and list out each unique date horizontally in my Portfolio page.
I found this formula that can search and list dates found in column A chronologically, but I have been unable to adapt it to my needs!
=INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,"<="&$A$2:$A$15),0))
Here is some sample data:
Portfolio Worksheet:
Trade & Dividend History
And here is a bonus Q if someone is able to answer:
I want to populate the Dividend History table, chronologically, with all dividends paid which are in a separate table per stock. Here is a sample of what two of the 14 identical tables look like:
Thanks in advance!
I am constructing a portfolio and I want to auto generate my positions. My goal is to determine my portfolio position at every trade date.
Context: I have two tables in a "Transaction History" worksheet, one with all the Trades and the other with all Dividends Received. In a separate "Portfolio" worksheet, I want to arrange every date where a transaction occurred, whether it was a trade or a dividend payment. The rest of that table auto-populates the change in cash and stock position based on the date.
The issue I'm having is to create a formula that will search both tables; Trades and Dividends, which are vertical, and list out each unique date horizontally in my Portfolio page.
I found this formula that can search and list dates found in column A chronologically, but I have been unable to adapt it to my needs!
=INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,"<="&$A$2:$A$15),0))
Here is some sample data:
Portfolio Worksheet:
![Lisgb7u.png](/board/proxy.php?image=https%3A%2F%2Fi.imgur.com%2FLisgb7u.png&hash=e71332cb342f704ff0a155ff672c7fce)
Trade & Dividend History
![YPvI3XE.png](/board/proxy.php?image=https%3A%2F%2Fi.imgur.com%2FYPvI3XE.png&hash=0573df21f9cde224ea3485fc88cd48f9)
And here is a bonus Q if someone is able to answer:
I want to populate the Dividend History table, chronologically, with all dividends paid which are in a separate table per stock. Here is a sample of what two of the 14 identical tables look like:
![8XP1X3G.png](/board/proxy.php?image=https%3A%2F%2Fi.imgur.com%2F8XP1X3G.png&hash=3f547b4eb1e230ced3089aac6b062224)
Thanks in advance!
Last edited: