Completed

Excel Macro to calculate share portfolio capital gains - FIFO

This project was successfully completed by deepbluetech for $75 USD in 3 days.

Get free quotes for a project like this
Employer working
Completed by:
Project Budget
$30 - $250 USD
Completed In
3 days
Total Bids
15
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).

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online