Excel Macro to calculate share portfolio capital gains - FIFO

IN PROGRESS
Bids
15
Avg Bid (USD)
$112
Project Budget (USD)
$30 - $250

Project Description:
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).

Skills required:
Excel, Finance, Visual Basic, Visual Basic for Apps
Additional Files: Fifo-TestCase.xlsx
Hire marbarak
Project posted by:
marbarak Israel
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


$ 66
in 3 days
Hire javed1705
$ 84
in 3 days
Hire mrbrave
$ 84
in 3 days
$ 75
in 3 days
$ 55
in 3 days
$ 131
in 3 days
$ 111
in 3 days
Hire Solver86
$ 55
in 3 days
$ 79
in 3 days
Hire vaibhav585
$ 30
in 1 days