Writing macros to calculate mutual fund performances

[danukachathu]

Hi All,

I am new to excel macros and have NO knowledge in programming!Any help/answers would be much appreciated.

Herewith I have attached an excel workbook. One worksheet represents one mutual fund. It contains stocks held monthly by this fund for 2003 - 2014 period. I have around 450 such mutual funds. Basically I need to calculate profits and stocks for each mutual fund.

• The first column (A) "ticker" shows the id for each stock and the worksheet is sorted by this first, and then by date.
• Do not worry about columns B, D and E.
• Column F shows the units bought by the fund, if any, at the particular date (date = Column D).
• Column G shows the units sold by the fund, if any, at the particular date.
• Column H shows the prices at which purchases and sales happened.
• The columns for which I need macros are I, J and K.
• I shows profits for a sale, J shows stock (number of stocks) and K shows the average price of the stock. This needs to be calculated based on FIFO (First in First Out) and Average Price methods.

The Excel workbook I have attached herewith have two worksheets where these have been calculated manually on the two methods. I have attached a word doc (FIFO&AVCO-Explains) which describes the two methods with a simple example as the equations in the worksheet at first glance could be confusing. There's another word doc (FIFO&AVCO-WithRealData) which also describes the equations I have used in the worksheets.

NOTE: The columns for which I need macros are I, J and K.the formulas in those columns(in RED) are only to show how calculation works.(you clear anytime)

Thanks in advance.

FIFO&AVCO-WithRealData.docx
FIFO&AVCO-Explains.docx
Book1.xlsx

PS Happy New Year 2016 to all members of the forum