Average and Total Calculation in Single Query (1 Viewer)

[555access]

INTRODUCTION TO DATABASE TABLE BEING USED -

I am working on a “Stock Market Prices” based Database Table. My table has got the data for the following FIELDS –
ID
SYMBOL
OPEN
HIGH
LOW
CLOSE
VOLUME
VOLUME CHANGE
VOLUME CHANGE %
OPEN_INT
SECTOR
TIMESTAMP

New data gets added to the table daily “Monday to Friday”, based on the stock market price changes for that day. The current requirement is based on the “VOLUME “field, which shows the volume traded for a particular stock on daily basis.

REQUIREMENT –
To get the Average and Total Volume for last 10,15 and 30 Days respectively.

METHOD USED CURRENTLY -
I created these 9 SEPARATE QUERIES in order to get my desired results –
First I have created these 3 queries to take out the most recent last 10,15 and 30 dates from the current table.
qryLast10DaysStored
qryLast15DaysStored
qryLast30DaysStored

Then I have created these 3 queries for getting the respective AVERAGES
qrySymbolAvgVolume10Days
qrySymbolAvgVolume15Days
qrySymbolAvgVolume30Days

And then I have created these 3 queries for getting the respective TOTALS
qrySymbolTotalVolume10Days
qrySymbolTotalVolume15Days
qrySymbolTotalVolume30Days

PROBLEM BEING FACED WITH CURRENT METHOD -
Now, my problem is that I have ended up having these so many different queries, whereas I wanted to get the output into One Single Query, as shown in the Snapshot of the Excel Sheet.

SOLUTION NEEDED -
Is there some way by which I can get these required fields into ONE SINGLE QUERY, so that I do not have to look into multiple places for the required fields? Can someone please tell me how to get all these separate queries into one -
A) Either by taking out or moving the results from these separate individual queries to one.
B) Or by making a new query which calculates all these fields within itself, so that these separate individual queries are no longer needed. This would be a better solution I think.

One Clarification about Dates – Some friend might think why I used the method of using Top 10,15 and 30 for getting the last 10,15 and 30 Date Values. Why not I just used the PC Date for getting these values? Or used something like - ("VOLUME","tbl-B", "TimeStamp BETWEEN Date() - 10 AND Date()")
The answer is that I require my query to "Read" the date from the "TIMESTAMP" Field, and then perform its calculations accordingly for LAST / MOST RECENT "10 days, 15 days, 30 days” FOR WHICH THE DATA IS AVAILABLE IN THE TABLE, WITHOUT BOTHERING WHAT THE CURRENT DATE IS. It should not depend upon the current date in any way.

If there is any better method or more efficient way to create these queries, then please enlighten.

Thanks a lot

PS : I have attached a SAMPLE database which has the data for 6 months, from 1 Nov 2011 to 30 April 2012 and it has also got the above mentioned queries in it.

 

[plog]

I can do it in 1 query using 2 VBA functions. Paste the below code into a module and save that module:

Function get_TotalVolume(Sym, VDate, Days)
    ' gets total volume of a symbol (Sym) for a certain period (Days) from a date (VDate)
 
ret = 0
SDate = DateAdd("d", -1 * (Days - 1), VDate)
    ' calculates first date of the timeframe
 
ret = DSum("[Volume]", "tbl-B", "[SYMBOL]='" & Sym & "' AND [TIMESTAMP]>=#" & SDate & "# AND [TIMESTAMP]<=#" & VDate & "#")
    ' gets total volume for a symbol from SDate to VDate
 
get_TotalVolume = ret
End Function
 
 
 
Function get_AvgVolume(Sym, VDate, Days)
    ' gets average volume of a symbol (Sym) for a certain period (Days) from a date (VDate)
 
ret = 0
SDate = DateAdd("d", -1 * (Days - 1), VDate)
    ' calculates first date of the timeframe
 
ret = get_TotalVolume(Sym, VDate, Days)
    ' gets total volume for a symbol
 
ret = ret / DCount("[Volume]", "tbl-B", "[SYMBOL]='" & Sym & "' AND [TIMESTAMP]>=#" & SDate & "# AND [TIMESTAMP]<=#" & VDate & "#")
    ' divides total volume by number of days data the timeframe covers
 
 
get_AvgVolume = ret
End Function

These functions takes a symbol, the last date of the time period, and how long you want that time period to be. So to calculate a 10 day average for ACC-1 with the last date of the period being 10/26/2012 you would use this code:

get_AvgVolume("ACC-1", 10/26/2012, 10)

To make a query getting total volume and average volume looking back on a 10 day period, this is the SQL:

SELECT [tbl-B].SYMBOL, [tbl-B].TIMESTAMP, get_TotalVolume([SYMBOL],[TIMESTAMP],10) AS TotVolume10Days, get_AvgVolume([SYMBOL],[TIMESTAMP],10) AS AvgVolume10Days
FROM [tbl-B];

To get the total volume for any other amount of days, replace the last argument of the function calls with the amount of days you want, i.e.:

get_AvgVolume([SYMBOL],[TIMESTAMP],100)
get_AvgVolume([SYMBOL],[TIMESTAMP],25)

 

[555access]

I can do it in 1 query using 2 VBA functions. Paste the below code into a module and save that module:

Function get_TotalVolume(Sym, VDate, Days)
    ' gets total volume of a symbol (Sym) for a certain period (Days) from a date (VDate)
 
ret = 0
SDate = DateAdd("d", -1 * (Days - 1), VDate)
    ' calculates first date of the timeframe
 
ret = DSum("[Volume]", "tbl-B", "[SYMBOL]='" & Sym & "' AND [TIMESTAMP]>=#" & SDate & "# AND [TIMESTAMP]<=#" & VDate & "#")
    ' gets total volume for a symbol from SDate to VDate
 
get_TotalVolume = ret
End Function
 
 
 
Function get_AvgVolume(Sym, VDate, Days)
    ' gets average volume of a symbol (Sym) for a certain period (Days) from a date (VDate)
 
ret = 0
SDate = DateAdd("d", -1 * (Days - 1), VDate)
    ' calculates first date of the timeframe
 
ret = get_TotalVolume(Sym, VDate, Days)
    ' gets total volume for a symbol
 
ret = ret / DCount("[Volume]", "tbl-B", "[SYMBOL]='" & Sym & "' AND [TIMESTAMP]>=#" & SDate & "# AND [TIMESTAMP]<=#" & VDate & "#")
    ' divides total volume by number of days data the timeframe covers
 
 
get_AvgVolume = ret
End Function

These functions takes a symbol, the last date of the time period, and how long you want that time period to be. So to calculate a 10 day average for ACC-1 with the last date of the period being 10/26/2012 you would use this code:

get_AvgVolume("ACC-1", 10/26/2012, 10)

To make a query getting total volume and average volume looking back on a 10 day period, this is the SQL:

SELECT [tbl-B].SYMBOL, [tbl-B].TIMESTAMP, get_TotalVolume([SYMBOL],[TIMESTAMP],10) AS TotVolume10Days, get_AvgVolume([SYMBOL],[TIMESTAMP],10) AS AvgVolume10Days
FROM [tbl-B];

To get the total volume for any other amount of days, replace the last argument of the function calls with the amount of days you want, i.e.:

get_AvgVolume([SYMBOL],[TIMESTAMP],100)
get_AvgVolume([SYMBOL],[TIMESTAMP],25)

Thanks for the help plog. I do not have much knowledge about the Database concepts etc. I tried to create a module as you told and then created a query by using the SQL code that you posted. Then I got the result as shown in the attached snapshot.

I am afraid that I might have made some mistake somewhere. It is showing the Fields in different way then what is required. The query should show only 27 Rows and not 3417. And for getting the other fields I think I need to modify the SQL, which I am currently trying and will update here about my progress.

If you notice what mistake I have made here then please correct me.

Thanks a lot for your help, I appreciate it a lot.

 

[plog]

You didn't make a mistake, I think you have it working perfectly. Why should it only show 27 rows and what 27 rows? Currently it is showing a row for every row in your table, if you want to limit it to less rows, I'd suggest creating another query based off that one.

 

[555access]

You didn't make a mistake, I think you have it working perfectly. Why should it only show 27 rows and what 27 rows? Currently it is showing a row for every row in your table, if you want to limit it to less rows, I'd suggest creating another query based off that one.

Thanks for the prompt reply plog.

The current database table has got the data for only 27 Stocks. So it should show only 27 Rows, one row for each symbol.

I would request you to have a look at the queries that I have created in the uploaded database. That will make it clear why it should show 27 Rows.

Thanks a lot

 

[plog]

First, quit posting my replies in their entirety. Everyone can scroll up to read what I wrote--let's cure the internet of this horrible practice.

Second, it doesn't clear it up, it raises a question--specifically, what 27 rows? My guess is you want the most recent dates of each SYMBOL. If so, you need to create a query off of tbl-B to find that data. Then you take that query and link it by both the SYMBOL and Maximum TIMESTAMP field to limit my query to just the most recent records of each symbol.

 

[555access]

I am sorry plog for posting your replies. I will keep that in mind in future.

Now here comes even better method ! Just one query to get all the required fields, no more messing around with multiple queries -

select
Symbol,
sum([10DayTotalVol]) as 10DayTotalV,
sum([10DayAvgVol]) as 10DayAvgV,
sum([15DayTotalVol]) as 15DayTotalV,
sum([15DayAvgVol]) as 15DayAvgV,
sum([30DayTotalVol]) as 30DayTotalV,
sum([30DayAvgVol]) as 30DayAvgV

from (

select
Symbol,
sum(volume) as 10DayTotalVol, avg(volume) as 10DayAvgVol,
0 as 15DayTotalVol, 0 as 15DayAvgVol,
0 as 30DayTotalVol, 0 as 30DayAvgVol
from
[tbl-b]
where
timestamp >= (select min(ts) from (select distinct top 10 timestamp as ts from [tbl-b] order by timestamp desc ))
group by
Symbol

UNION

select
Symbol,
0, 0,
sum(volume), avg(volume),
0, 0
from
[tbl-b]
where
timestamp >= (select min(ts) from (select distinct top 15 timestamp as ts from [tbl-b] order by timestamp desc ))
group by
Symbol

UNION

select
Symbol,
0, 0,
0, 0,
sum(volume), avg(volume)
from
[tbl-b]
where
timestamp >= (select min(ts) from (select distinct top 30 timestamp as ts from [tbl-b] order by timestamp desc ))
group by
Symbol
) s

group by
Symbol

ExactaBox @stackoverflow Rocks ! :thumbup:

Thanks

 

[plog]

no more messing around with multiple queries

My solution was one query. If you count every 'Select' as a query then this new solution is actually 10 queries. This solution will only work for the most recent SYMBOL records in your data--you will not be able to get what the Total and Average Volume was for records in the past. It's also pretty messy--do you understand it? Will you be able to modify it if you need to get a 45 day average.

Worst of all, I'm not certain its giving you correct averages. For example, the 10 day total and average looks to be based on the last 10 records in your data, not records that occured in the last 10 days.

 

[555access]

It seems that you do not understand how the Stock Market Data works. The solution will obviously show the data for the most recent SYMBOL records only, and that is how it is supposed to be.

Worst of all, I'm not certain its giving you correct averages. For example, the 10 day total and average looks to be based on the last 10 records in your data, not records that occured in the last 10 days.

You might not have read what I explained in the very first post regarding the Dates. I mentioned very clearly that -

The answer is that I require my query to "Read" the date from the "TIMESTAMP" Field, and then perform its calculations accordingly for LAST / MOST RECENT "10 days, 15 days, 30 days” FOR WHICH THE DATA IS AVAILABLE IN THE TABLE, WITHOUT BOTHERING WHAT THE CURRENT DATE IS. It should not depend upon the current date in any way.

Hope you understand it now. I appreciate the help that you provided.

Thanks

PS: you asked me not to use quotes, but since you seems to miss the very obvious requirement, that's why I am quoting those specific lines again.