All,
I'm trying to replicate accounting logic in Access. Here is the goal:
Take an opening Position - Say 20 Shares of IBM on 1/1/2019 and calculate the market value for each business day of the rest of the month.
The math for each day is ( Prior Day-end Shares + The Share impact (+/-) of any purchases or sales for the current day ) x Current Day-end Price.
I have a table of day-end prices, so the trick is calculating each day-end quantity after 1/1/19.
Sample activity
1/3/19 buy 10 shares
1/5/19 sell 3 shares
The result should be rows of data like this (Date / Shares / Price / Value)
1/1 20 50 $1000
1/2 20 51 $1020
1/3 30 49 $1470
1/4 30 48 $1440
1/5 27 49 $1323
1/6 27 48 $1248
1/7 27 49 $1323
......... and so on
1/31 27 50 $1350
Obviously this is easy in XL since you can reference other rows, but I need to keep this all in Access.
Any help is appreciated
I'm trying to replicate accounting logic in Access. Here is the goal:
Take an opening Position - Say 20 Shares of IBM on 1/1/2019 and calculate the market value for each business day of the rest of the month.
The math for each day is ( Prior Day-end Shares + The Share impact (+/-) of any purchases or sales for the current day ) x Current Day-end Price.
I have a table of day-end prices, so the trick is calculating each day-end quantity after 1/1/19.
Sample activity
1/3/19 buy 10 shares
1/5/19 sell 3 shares
The result should be rows of data like this (Date / Shares / Price / Value)
1/1 20 50 $1000
1/2 20 51 $1020
1/3 30 49 $1470
1/4 30 48 $1440
1/5 27 49 $1323
1/6 27 48 $1248
1/7 27 49 $1323
......... and so on
1/31 27 50 $1350
Obviously this is easy in XL since you can reference other rows, but I need to keep this all in Access.
Any help is appreciated