You have chosen to sponsor your bid up to a maximum amount of .
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).