Help with VBA user defined function - iterate by date and multiply field of one table by another table

[micklebt]

My project is the conversion of an investment management decision-making tool from Excel to Access.

I have two Access tables:

[tbl_POSITIONS] records the closing value of each position held at market close daily: eg: 12/15/12, AIGTX, 41.17

​

[tbl_RETURNS] records the date and amount when dividends, short term capital gains, and long term capital gains are awarded: eg: 12/7/12, AIGTX, .00213
​

My objective is a UDF which cycles through tbl_POSITIONS, and when a fund and date match are found, from dividend issuance date forward for that fund the function multiplies the NAV (41.17) by the dividend (.00213) each day until a new dividend is issued.

When a new subsequent dividend is awarded, the function will compound the returns by multiplying date period 1 by dividend 1, and date period 2 by dividend 1 +dividend 2.

In English, I believe would go like this: for each holding where tbl_POSITIONS date => tbl_RETURNS date, then multiply NAV x dividend.

When a second dividend date for a given holding is reached, the new multiplier becomes (dividend 1 + dividend 2), and so on.

The intention is a calculated query column which accurately produces calculated NAV on any given date by factoring and applying the issuance of dividends back into the NAV.


Any help is greatly appreciated. Thanks,

Brian

12/10/12	VIPSX	15.14
12/10/12	VFSTX	10.88
12/10/12	VFINX	131.41
12/10/12	VISGX	24.63
12/10/12	VFITX	11.84
12/10/12	VIGRX	36.64
12/10/12	VGTSX	14.73
12/10/12	VGSIX	21.65
12/10/12	VIMSX	22.41
12/10/12	WASCX	24.98
12/10/12	VISVX	17.42
12/10/12	WBFFX	21.51
12/10/12	VLACX	26.31
12/10/12	WAAEX	43.30
12/10/12	VUSTX	13.59

<tbody>
[TH="bgcolor: #c0c0c0"] ClosingDate [/TH]
[TH="bgcolor: #c0c0c0"] Ticker [/TH]
[TH="bgcolor: #c0c0c0"] NAV [/TH]

</tbody>

1/31/2007	VBIIX	0.043	1
1/31/2007	VBISX	0.038	1
1/31/2007	VBLTX	0.052	1
2/16/2007	AFIFX	0.1192	1
2/28/2007	BPRAX	0.007622	1
2/28/2007	CPTNX	0.038168	1
2/28/2007	FFIHX	0.0292	1
2/28/2007	GTDTX	0.029355575	1
2/28/2007	OGGAX	0.038	1
2/28/2007	OIEIX	0.02365	1
2/28/2007	VBIIX	0.039	1
2/28/2007	VBISX	0.035	1
2/28/2007	VBLTX	0.047	1
3/13/2007	TWEAX	0.0174	1
3/20/2007	SASVX	0.019682	1
3/21/2007	NAESX	0.006	1

<tbody>
[TH="bgcolor: #c0c0c0"] ClosingDate [/TH]
[TH="bgcolor: #c0c0c0"] Ticker [/TH]
[TH="bgcolor: #c0c0c0"] Returns [/TH]
[TH="bgcolor: #c0c0c0"] ReturnTypeID [/TH]

</tbody>

 

[xenou]

If you have a stock that's paying dividends of 1/5 of a percent on the dollar every day don't stop to write VBA UDFs - at that rate a $40 investment would more than double in a year: $41 * (1 + .002) ^ 365 = $85

 

[micklebt]

Perhaps I'm not clear with my example.

The objective is to track chronologically the changing value of financial holding from it's inception date to present by tracking the posted closing value AND applying any dividends issued from that date forward.

The investment's value changes in two ways, daily increasing or decreasing value at closing bell, and periodic issuance of dividends, splits, etc. We are tracking a net accumulated value over time, which is the reason for my UDF request.

If I knew how to do so, the solution may exist with a query - but that's beyond my ability - which is why I envision a UDF solution. Unfortunately, my vision exceeds my skills in executing! LOL.

Hope this helps anyone else who is able to lend a hand.

Many, many thanks in advance!

Brian

 

[xenou]

I don't really understand this. Why do you have dividends being issued every day? That doesn't happen. Strictly speaking they aren't part of the stocks value either - they are part of your return but the stock itself represents the value of the company - i.e., in the extreme case if a company liquidates all it's assets and returns them to shareholders as dividends then the stock is worth zero. So you can measure the gain/loss at a given date, and the value of dividends issued, to measure your return (not the value of the stock).