Accounting Logic (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 06:11
Joined
Sep 15, 2012
Messages
229
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
 

plog

Banishment Pending
Local time
Today, 05:11
Joined
May 11, 2011
Messages
11,611
You can obtian prior records/values in Access queries--as long as you have a valid definition of "prior". As long as you have a Date field this should suffice to find prior values.

But to do so we need more information. You provided expected results, but to get a good grasp of the issue, we need starting data. Can you provide that? Include all tables/fields relevant.
 

Users who are viewing this thread

Top Bottom