Writing macros to calculate mutual fund performances

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

I am trying to follow the logic - and se3quence - of your calcs, and not succeeding too well

Take column I...
You have a bunch of 0's (becasue you had no sales?)
Then you have, spread about in that column, a bunch of disparate formulas...
I5=(H5-H3)*G5
I9=((H9-H3)*(F3-G5))+((H9-H4)*F4)+((H9-H6)*9)
I11=((H11-H6)*(F6-9))+((H11-H7)*F7)+((H11-H8)*F8)
I24=(H24-H19)*G24
I25=((H25-H19)*(F19-G24))+((H25-H20)*F20)+((H25-H21)*F21)+((H25-H22)*F22)+((H25-H23)*F23)
I see no real progression/logic in those. Sometimes you use references, other times, hard-coded numbers...someimes a simple formula, others, a long nested formula
While these must make perfect sense to you (and are probably just calcs you made up to show the answers), most of us will probably have no clue what you are doing

Column K has some equally mistifying calcs...
K3=(F3*H3)/J3
K4=((F3*H3)+(F4*H4))/J4
K5=(((F3-G5)*H3)+(F4*H4))/J5
K6=(((F3-G5)*H3)+(F4*H4)+(F6*H6))/J6


If you can walk me through the sequence and the calc, I may be able to help