Domains - Aggregating, Averaging and Counting

[Jackson via AccessMonster.com]

Hi,

I have a table that is going to have a date stamp for each record, a book
like Canada Equity and a Market Value Amount.

What I want to do is find the Month to Date Average Market Value Amount. The
month to date part I can simply do via criteria in the query, however,
finding the average is the problem for me. Lets take today for example. Using
Close of Business data, there are 3 business days that have fully completed.
For most books, this would simply be the sum of the MV/3 days of records. But,
if a book has only had a MV for 2 days of the month, it would only need to
calculate this based on those two days, not the sum of the MVs/3. Thus I
guess I need to somehow count the number of occurances for each book and each
date stamp month to date....

Any suggestions on how to go about this? Any help would be appreciated.

Cheers.

 

[Michel Walsh]

In theory, AVG should divide the sum by the number of 'records', so, if
there is only one record per day, that could be something like:



SELECT Format(dateTimeStamp, "mm-yyyy"), AVG(amounts)
FROM myTable
GROUP BY Format(dateTimeStamp, "mm-yyyy")


to average the amounts, per month (and year).



If there are more than one records per day, first aggregate the amount, per
day:


SELECT DateValue(dateTimeStamp), AVG(amounts) as AvgInDay
FROM myTable
WHERE dateTimeStamp <= iif( Now < Today + #18:00:00#, Today,
Today+#18:00:00#)
GROUP BY DateValue(dateTimeStamp)



Note that a record with a dateTimeStamp of today date, but with a time less
than 18:00:00 ( 6PM), will not be taken into account , unless the PC clock
is past 18:00:00. Doing so, we don't average on today data if it is only
13:00:00, since all the data, about today, is not in the database yet. That
is just to show that it is possible to dynamically take count of "days that
have fully completed", or not, and it may be NOT appropriate for your case.




Hoping it may help,
Vanderghast, Access MVP

 

[jackwoodhead via AccessMonster.com]

Ah, thanks for this, didn't realise AVG function was available for
aggregation...

In theory, AVG should divide the sum by the number of 'records', so, if
there is only one record per day, that could be something like:

SELECT Format(dateTimeStamp, "mm-yyyy"), AVG(amounts)
FROM myTable
GROUP BY Format(dateTimeStamp, "mm-yyyy")

to average the amounts, per month (and year).

If there are more than one records per day, first aggregate the amount, per
day:

SELECT DateValue(dateTimeStamp), AVG(amounts) as AvgInDay
FROM myTable
WHERE dateTimeStamp <= iif( Now < Today + #18:00:00#, Today,
Today+#18:00:00#)
GROUP BY DateValue(dateTimeStamp)

Note that a record with a dateTimeStamp of today date, but with a time less
than 18:00:00 ( 6PM), will not be taken into account , unless the PC clock
is past 18:00:00. Doing so, we don't average on today data if it is only
13:00:00, since all the data, about today, is not in the database yet. That
is just to show that it is possible to dynamically take count of "days that
have fully completed", or not, and it may be NOT appropriate for your case.

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 19 lines]

Cheers.

 

[Jackson via AccessMonster.com]

Michael,

Do you know if it is possible to create a geometric average? For example, if
I have a list of stocks, their MV (exposure) and P&L on the day. The return
for the day is simple P&L/MV in percentage format. How then in my total query
would I be able to do the average such:

(N days)√((Day 1 Return)*(Day 2 Return)*…*(Day N Return))

Would I have to create an aggregate geo avg function in VBA first?

Cheers.

In theory, AVG should divide the sum by the number of 'records', so, if
there is only one record per day, that could be something like:

SELECT Format(dateTimeStamp, "mm-yyyy"), AVG(amounts)
FROM myTable
GROUP BY Format(dateTimeStamp, "mm-yyyy")

to average the amounts, per month (and year).

If there are more than one records per day, first aggregate the amount, per
day:

SELECT DateValue(dateTimeStamp), AVG(amounts) as AvgInDay
FROM myTable
WHERE dateTimeStamp <= iif( Now < Today + #18:00:00#, Today,
Today+#18:00:00#)
GROUP BY DateValue(dateTimeStamp)

Note that a record with a dateTimeStamp of today date, but with a time less
than 18:00:00 ( 6PM), will not be taken into account , unless the PC clock
is past 18:00:00. Doing so, we don't average on today data if it is only
13:00:00, since all the data, about today, is not in the database yet. That
is just to show that it is possible to dynamically take count of "days that
have fully completed", or not, and it may be NOT appropriate for your case.

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 19 lines]

Cheers.

 

[Jackson via AccessMonster.com]

Also have one other question you may be able to help me with...maybe...

If I have each security datestamped but want to find the change in MV
exposure between day 1 and yesterday, if there an easy way to do this in a
query - or would I have to build two queries, one for each date, join them in
a 3rd and them take the difference?

Cheers,
Jack.

 

[Michel Walsh]

Geometric average on data >0, yes:

EXP(AVG(LOG(x)))


should do.



Hoping it may help,
Vanderghast, Access MVP

Michael,

Do you know if it is possible to create a geometric average? For example,
if
I have a list of stocks, their MV (exposure) and P&L on the day. The
return
for the day is simple P&L/MV in percentage format. How then in my total
query
would I be able to do the average such:

(N days)?((Day 1 Return)*(Day 2 Return)*.*(Day N Return))

Would I have to create an aggregate geo avg function in VBA first?

Cheers.

In theory, AVG should divide the sum by the number of 'records', so, if
there is only one record per day, that could be something like:

SELECT Format(dateTimeStamp, "mm-yyyy"), AVG(amounts)
FROM myTable
GROUP BY Format(dateTimeStamp, "mm-yyyy")

to average the amounts, per month (and year).

If there are more than one records per day, first aggregate the amount,
per
day:

SELECT DateValue(dateTimeStamp), AVG(amounts) as AvgInDay
FROM myTable
WHERE dateTimeStamp <= iif( Now < Today + #18:00:00#, Today,
Today+#18:00:00#)
GROUP BY DateValue(dateTimeStamp)

Note that a record with a dateTimeStamp of today date, but with a time
less
than 18:00:00 ( 6PM), will not be taken into account , unless the PC
clock
is past 18:00:00. Doing so, we don't average on today data if it is only
13:00:00, since all the data, about today, is not in the database yet.
That
is just to show that it is possible to dynamically take count of "days
that
have fully completed", or not, and it may be NOT appropriate for your
case.

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 19 lines]

Cheers.

 

[Michel Walsh]

It don't see what a MV is, but I assume you can bring two references to your
table (one will get an _1 appended to its name, if you do it graphically),
and impose a criteria such that:

TableName.DateStamp = 1+ TableName_1.DateStamp


so that a record in TableName can now be seen as the record of the day after
the one in TableName_1, and thus, the day to day change is:


TableName.amount - TableName_1.amount



Sure, you can put the criteria as a join, but doing so, you loose the
graphical editor, so, may be better to keep it as criteria, in the where
clause, mainly if you don't need an outer join.



Hoping it may help,
Vanderghast, Access MVP

 

[jackwoodhead via AccessMonster.com]

HI Michael,

Thanks for this. Sorry, MV = Market Value or the Exposure to a particular
Asset. Just checking we're on the same page: if we pretend I have a table
tblPositions with 3 days of data, 01/03/07, 02/03/07 & 05/03/07 (stored under
ImportDate in the table). Let's also say we only have 1 stock, ABC. It's
values respective to above dates are 10,20 & 20. Can I in a single query run
for say 02/03/07:

Date Stock MV Change in MV
02/03/07 ABC 20 10

Then if run on 05/03:

Date Stock MV Change in MV
02/03/07 ABC 20 0

I was going to do this with 3 queries basically, the third appending this
data back into the original table as a new field (Starting MV), using two
seperate queries between Day 2 and Day 1 to calculate - but it sounds like I
may be doing this a long way around?

Thanks.

It don't see what a MV is, but I assume you can bring two references to your
table (one will get an _1 appended to its name, if you do it graphically),
and impose a criteria such that:

TableName.DateStamp = 1+ TableName_1.DateStamp

so that a record in TableName can now be seen as the record of the day after
the one in TableName_1, and thus, the day to day change is:

TableName.amount - TableName_1.amount

Sure, you can put the criteria as a join, but doing so, you loose the
graphical editor, so, may be better to keep it as criteria, in the where
clause, mainly if you don't need an outer join.

Hoping it may help,
Vanderghast, Access MVP

Also have one other question you may be able to help me with...maybe...

[quoted text clipped - 6 lines]

Cheers,
Jack.

 

[Michel Walsh]

Since the dates are not necessary consecutive, we cannot use 1+, as I
described earlier, but it is still doable:



SELECT a.Stock,
a.Date,
LAST(a.MV),
LAST(a.MV)-LAST(b.MV) As ChangeInMV

FROM (mytable As a LEFT JOIN myTable As b ON a.Stock=b.Stock AND a.Date >
b.Date)
LEFT JOIN myTable As c ON a.Stock=c.Stock AND a.Date> c.Date

GROUP BY a.Stock, a.Date, b.Date

HAVING b.Date=MAX(c.Date)


We have 3 reference to the table, a, b and c. We impose the relations:

a.Stock=b.Stock AND a.Date > b.Date

and

a.Stock=c.Stock AND a.Date> c.Date

So, to have a 'match', the records will be about the same Stock value, and
reference b and c will be record dated BEFORE the date in a.

If we look at what occur for one value of a.Date (GROUP BY), we keep only
the groups having b.date = MAX(c.date), which is we keep only the closest
date, in b, that occurs before a.date, which we can refer, in short, by the
"previous date". So, reference b is about the previous date of what we have,
in reference a, and the change is thus: a.MV-b.MV. We used LAST to satisfy
the syntax which requires the fields are either aggregated, either in the
GROUP BY. It would have been possible to add a.MV and b.MV in the GROUP BY,
but that slows the query execution (adding uselessly fields in the GROUP BY
clause).




Hoping it may help,
Vanderghast, Access MVP

HI Michael,

Thanks for this. Sorry, MV = Market Value or the Exposure to a particular
Asset. Just checking we're on the same page: if we pretend I have a table
tblPositions with 3 days of data, 01/03/07, 02/03/07 & 05/03/07 (stored
under
ImportDate in the table). Let's also say we only have 1 stock, ABC. It's
values respective to above dates are 10,20 & 20. Can I in a single query
run
for say 02/03/07:

Date Stock MV Change in
MV
02/03/07 ABC 20 10

Then if run on 05/03:

Date Stock MV Change in
MV
02/03/07 ABC 20 0

I was going to do this with 3 queries basically, the third appending this
data back into the original table as a new field (Starting MV), using two
seperate queries between Day 2 and Day 1 to calculate - but it sounds like
I
may be doing this a long way around?

Thanks.

It don't see what a MV is, but I assume you can bring two references to
your
table (one will get an _1 appended to its name, if you do it graphically),
and impose a criteria such that:

TableName.DateStamp = 1+ TableName_1.DateStamp

so that a record in TableName can now be seen as the record of the day
after
the one in TableName_1, and thus, the day to day change is:

TableName.amount - TableName_1.amount

Sure, you can put the criteria as a join, but doing so, you loose the
graphical editor, so, may be better to keep it as criteria, in the where
clause, mainly if you don't need an outer join.

Hoping it may help,
Vanderghast, Access MVP

Also have one other question you may be able to help me with...maybe...

[quoted text clipped - 6 lines]

Cheers,
Jack.

 

[jackwoodhead via AccessMonster.com]

Hi Michael,

I'm a little confused, what do you mean by a & b? I've only got one table so
far...?

Since the dates are not necessary consecutive, we cannot use 1+, as I
described earlier, but it is still doable:

SELECT a.Stock,
a.Date,
LAST(a.MV),
LAST(a.MV)-LAST(b.MV) As ChangeInMV

FROM (mytable As a LEFT JOIN myTable As b ON a.Stock=b.Stock AND a.Date >
b.Date)
LEFT JOIN myTable As c ON a.Stock=c.Stock AND a.Date> c.Date

GROUP BY a.Stock, a.Date, b.Date

HAVING b.Date=MAX(c.Date)

We have 3 reference to the table, a, b and c. We impose the relations:

a.Stock=b.Stock AND a.Date > b.Date

and

a.Stock=c.Stock AND a.Date> c.Date

So, to have a 'match', the records will be about the same Stock value, and
reference b and c will be record dated BEFORE the date in a.

If we look at what occur for one value of a.Date (GROUP BY), we keep only
the groups having b.date = MAX(c.date), which is we keep only the closest
date, in b, that occurs before a.date, which we can refer, in short, by the
"previous date". So, reference b is about the previous date of what we have,
in reference a, and the change is thus: a.MV-b.MV. We used LAST to satisfy
the syntax which requires the fields are either aggregated, either in the
GROUP BY. It would have been possible to add a.MV and b.MV in the GROUP BY,
but that slows the query execution (adding uselessly fields in the GROUP BY
clause).

Hoping it may help,
Vanderghast, Access MVP

HI Michael,

[quoted text clipped - 50 lines]

 

[Michel Walsh]

Yes, but you can use three 'markers' on it. The a, b and c are just alias
for these (moving) 'markers', you don't have to change their names, they are
defined, in SQL, by using the AS keyword:


..... myTable As a .... myTable As b .... myTable As c ....


In fact, just change myTable by your real table name should do (myTable
appears 3 times, so 3 substitutions).



Hoping it may help,
Vanderghast, Access MVP

Hi Michael,

I'm a little confused, what do you mean by a & b? I've only got one table
so
far...?

Since the dates are not necessary consecutive, we cannot use 1+, as I
described earlier, but it is still doable:

SELECT a.Stock,
a.Date,
LAST(a.MV),
LAST(a.MV)-LAST(b.MV) As ChangeInMV

FROM (mytable As a LEFT JOIN myTable As b ON a.Stock=b.Stock AND a.Date >
b.Date)
LEFT JOIN myTable As c ON a.Stock=c.Stock AND a.Date> c.Date

GROUP BY a.Stock, a.Date, b.Date

HAVING b.Date=MAX(c.Date)

We have 3 reference to the table, a, b and c. We impose the relations:

a.Stock=b.Stock AND a.Date > b.Date

and

a.Stock=c.Stock AND a.Date> c.Date

So, to have a 'match', the records will be about the same Stock value, and
reference b and c will be record dated BEFORE the date in a.

If we look at what occur for one value of a.Date (GROUP BY), we keep only
the groups having b.date = MAX(c.date), which is we keep only the closest
date, in b, that occurs before a.date, which we can refer, in short, by
the
"previous date". So, reference b is about the previous date of what we
have,
in reference a, and the change is thus: a.MV-b.MV. We used LAST to
satisfy
the syntax which requires the fields are either aggregated, either in the
GROUP BY. It would have been possible to add a.MV and b.MV in the GROUP
BY,
but that slows the query execution (adding uselessly fields in the GROUP
BY
clause).

Hoping it may help,
Vanderghast, Access MVP

HI Michael,

[quoted text clipped - 50 lines]

Cheers,
Jack.