Excel Macro to calculate share portfolio capital gains - FIFO

In Progress

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: Excel, Finance, Visual Basic, Visual Basic for Apps

See more: fifo excel, excel fifo, capital gains excel worksheet, fifo capital gains excel, fifo macro excel, excel macro fifo, excel fifo open value sample, excel fifo macro, excel calculate portfolio gain, calculations capital gains excel, capital gains fifo, excel fifo add, calculate fifo excel, capital gains worksheet excel, portfolio excel fifo matching, excel capital gain share, calculate fifo gains excel, write macro calculate capital gains, capital gain fifo excel, portfolio macro excel, fifo excel macro, sample use case specification, sample portfolio, sample of a portfolio, portfolio sample

Project ID: #4495581

Awarded to:


I am an Excel macro expert and can take on the job. Let me know.

$75 USD in 3 days
(99 Reviews)

13 freelancers are bidding on average $123 for this job


Looking forward to working with you...

$66 USD in 3 days
(63 Reviews)

please check pm

$84 USD in 3 days
(67 Reviews)

HI....I have understood all requirements and am available to start..My only question in message box.

$84 USD in 3 days
(83 Reviews)

Hi, I am interested to work for your project . I am an expert in excel and VBA Programming in Excel. I have experience working on lot of Excel based projects. I will make sure that I deliver you the best with 100% More

$55 USD in 3 days
(43 Reviews)

Sir, I can do the project. Refer PMB. Looking for further discussions in this matter. with thanks and regards

$131 USD in 3 days
(17 Reviews)

excel macro expert ..............

$111 USD in 3 days
(15 Reviews)

****** Please check details on PM. Thanks ******

$55 USD in 3 days
(9 Reviews)

Dear friend, I can do this job because I have a great deal of experience in this area. I made some programs in Excel 2010 and successfully sold them. I know the insights. I hope to start this work as soon as possible. More

$79 USD in 3 days
(2 Reviews)

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

$30 USD in 1 day
(1 Review)

Hi, Please check your PM

$220 USD in 6 days
(0 Reviews)

Hello Marbark, I can work on this.Lets Start working on this. Please check my PM for more information

$247 USD in 3 days
(0 Reviews)

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.

$165 USD in 3 days
(0 Reviews)

Hi, I have experience working in VBA scripts for automation. Please see PM.

$242 USD in 21 days
(0 Reviews)

let's do it

$35 USD in 7 days
(0 Reviews)