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

Thanks in advance.

15078
15079
15080


PS Happy New Year 2016 to all members of the forum

[SamT]

Unfortunately for VBA programmers, you have purchase cost and sales price in the same column. That makes it a very complex problem. IF you can insert a Cost column before the Price column, a simple VBA Macro can move the Cost values into the Cost column.

With separate columns for Purchase Cost and Sales Price the very complex code becomes merely a little complex.

On further consideration, I think the best all around solution is to add a "Holdings by Cost" database sheet to the workbook.

Below is a graphical representation of the first two lines of that database sheet as it evolves over time, using the values and timeline as in your attachment, "Book1.xlsx."



Before first sale








Holding
3IINFOTECH
10000
13





Cost
3IINFOTECH
88.2
79.8





After first sale








Holding
3IINFOTECH
9978
13





Cost
3IINFOTECH
88.2
79.8





after next buys








Holding
3IINFOTECH
9978
13
10
3
1


Cost
3IINFOTECH
88.2
79.8
89.1
73.65
78.5


After Next Sale








Holding
3IINFOTECH
1
3
1




Cost
3IINFOTECH
89.1
73.65
78.5




After last Sale








Holding
3IINFOTECH







Cost
3IINFOTECH