danukachathu

01-02-2016, 04:05 PM

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 can clear anytime)

Thanks in advance.

15078

15079

15080

PS Happy New Year 2016 to all members of the forum

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 can clear anytime)

Thanks in advance.

15078

15079

15080

PS Happy New Year 2016 to all members of the forum