Excel Macro to calculate share portfolio capital gains - FIFO
Create an excel Macro to analyze "Buy" and "Sell" Share transactions and calculate the Capital Gain using the FIFO method.
Description of the Input:
A] "Data" Worksheet - Consists of Buy and Sell transaction
The financial instrument (Share) traded is uniquely identified by the combination of the 2 fields: "Instrument", "Venue"
B]"Data-ILS Rate" Worksheet - Consists of the USD to ILS (Israel Shekel) Exchange rate for each date.
Create an excel Macro according to the following specification:
- Analyzes the data in a Worksheet called "Data"
- Sort the Buy and Sell transactions according to "Value Date"
- For each row, determine the transaction type:
- If the first appearance of a financial instrument is a "Bought" transaction, understand that this is Transaction Type "Long"
- If the first appearance of a financial instrument is a "Sold" transaction, understand that this is Transaction Type "Short"
- Continue processing using FIFO to determine position closure (Selling a "Long" position or Buying to cover "Short" position)
- Write to a Worksheet named "Results":
- List of matched transactions, e.g. Bought+Sold a certain instrument
- Add information about the ILS (Israel Shekel) Exchange rate in the open and close dates (using the "Data-ILS Rate" worksheet)
- If there are positions that were not closed, add their information to the "Open Positions" section
- Note: In case there is no matching ILS conversion quote for the date, use the previous available date.
For example: a transaction was executed on Jan 7 2012. However, there is no entry for this date in "Data-ILS Rate".
Solution: Use the previous available date (Jan 6 2012)
In order to clarify, I have included 3 examples:
Scenario 1 & 2 displays a short list of transactions and the expected outcome after the Macro execution.
In some of the cells I added formulas in order to assist you in understanding the calculations (those cells are marked with Bold+italics)
Scenario 3 is real world data. After making sure the Macro solves scenario 1 & 2 correctly, run it against scenario 3.
In addition, Worksheet "Data" includes sample data and Worksheet "Results" the desired outcome.
I also included a Worksheet with forum links providing code snippets
Bonus: When reading the input, Macro should work according to row headers and not absolute location (i.e. "Currency" and not column A).
13 freelancers are bidding on average $123 for this job
Team of vba experts will complete this task in 12 hours , we have read you job posting and ready to provide sample !! let me know when to start !! also see PM
I have excellent Excel Macro skills and good financial knowledge. You can trust me with the highest quality work. Please connect with me if you would like to see sample of the my excel/macro works.