The script will process Buy/Sell of financial securities (Stocks, Options etc.) in order to create a table displaying the current status of the portfolio (open positions and cash).
There are currently 8 possible Transaction types:
3. Deposit (Cash in)
4. Withdraw (Cash out)
5. Forex (Convert between two currencies)
6. Dividend (Cash in)
7. Fee deduction (Cash out)
8. Tax deduction (Cash out)
In the example file, transaction data is in columns B-L while the rest of the columns (and colors) are used for explanation.
The result table (the portfolio table) is placed in row 23 downwards but in the actual macro it should be placed in a new sheet.
The "tricky" part is that in the portfolio table, the "Average Cost" field for the security needs to be calculated using FIFO.
So, for example,
if I buy a certain security in two different transactions ("layers") first is 100, second is 500 and Sell 150 stocks afterwards,
the first layer sold is from the 100 trans. and the remaining 50 from the 500 trans.
The script also needs to do a lookup in a table stored in the "Symbols" sheet, and populate the NewField1..NewField5 columns with the right value (a default value is also provided).
Cash balances are recorded on rows 30 downwards.
Thank you for your time!
12 freelancers are bidding on average $202 for this job
I can fix this. I need little more time to do it. Since this involve lot of testing. If you are ok with time then please provide the files and I will do it.