Comparing a day's fields with the previous day's fields

  • Thread starter Thread starter Anon
  • Start date Start date
A

Anon

Hi,

I have a table with a lot of stock data in it, and need to do calculations
between adjacent days.

For example, one result that I need to pull from the database is the
difference between two adjacent days closing prices.

Adjacent days are not always 1 day apart, and this is giving me some trouble
with writing a query which doesn't hang my machine.

How do I do this in an optimal fashion? There are thousands of rows in my
table.

Some sample data from the table is below:

ID symbolID quotedate open high low close volume
adjustedClose
1 124 25 November 2003 8778.29 8778.29 8778.29 8778.29
0 8778.29
2 124 26 November 2003 8768.56 8768.56 8768.56 8768.56
0 8768.56
3 124 27 November 2003 8759.74 8759.74 8759.74 8759.74
0 8759.74
4 124 28 November 2003 8770.85 8770.85 8770.85 8770.85
0 8770.85
5 124 01 December 2003 8891.18 8891.18 8891.18 8891.18
0 8891.18
6 124 02 December 2003 8744.28 8744.28 8744.28 8744.28
0 8744.28
7 124 03 December 2003 8818.29 8818.29 8818.29 8818.29
0 8818.29

Thanks,

Anon.
 
I have a table with a lot of stock data in it, and need to do calculations
between adjacent days.

For example, one result that I need to pull from the database is the
difference between two adjacent days closing prices.

Adjacent days are not always 1 day apart, and this is giving me some trouble
with writing a query which doesn't hang my machine.

How do I do this in an optimal fashion? There are thousands of rows in my
table.
Click to expand...

A Self Join query joining the table to itself on the SymbolID with a
criterion on the date of

=DMax("[Quotedate]", "[yourtable]", "[Quotedate] < #" & [Quotedate] &
"#")

Be sure that Quotedate is indexed (nonuniquely). Even so, don't expect
this to run very fast!
 
Back
Top