Formula for calculating cumulative volume and averages

[XC80]

Hi,

I'm trying to create a database that will calculate an ongoing volume and average price for stocks that I am trading.

For example,
I have been buying stock ABC over the past 5 days and want to know what my current vol and avg price is.


I want to create a form where I can enter the following fields each day:

Ticker
Traded Shares
Traded Price

Once I put in the Ticker, Traded Shares, and Traded Price, I'd like to see an output that shows my total volume and avg price for all the previous days I've traded the same symbol.

I was able to do this in excel but its very ugly. I was hoping I could do this in access but am unable to figure out how to get this going.

Thanks

[ranman256]

you would add the daily prices everyday to a table.

then make a query on that table that Sums the volume, and Avg price.

[June7]

Or build a report with Grouping & Sorting features and aggregate calcs in header/footer sections. Report allows display of detail records as well as summary data.

[XC80]

Thanks... I have the table populated. How/where do I write this query?

Also, I have a column that has my initial order... Say 100,000 shares of ABC.
Once this order is filled and say I get a new order at a future date for 125,000 shares of ABC, how do I get Access to start over when finding the new vol and avg price?

[ranman256]

start new query
add the table
bring down the fields you want
click the grouping (sum) icon
change to SUM or AVG under your field.

(also: google building queries in Access)

[June7]

What do you mean by 'start over'?

Apply date range filter criteria to the dataset if you want to limit calculation to a specific period.

[XC80]

My first order could be to buy 500,000 shares of ABX.
If this order takes me two days to complete and on
DAY 1: I buy 250,000 shares at $14.00
DAY 2: I buy 250,000 shares at $14.02
This would complete my 500,000 shares order @ an average price of $14.01.

At some point in the future I can get another order to buy 750,000 shares of ABX, and this could take me a few days to complete but I wouldn't want any of the new order to include the previous order of 500,000 that I've already completed.

[June7]

That would be 3 records in the table.

Apply filter criteria that excludes records you don't want in the calculation.