By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

find consecutive events and see what happens later

P: n/a
Hi All,

I want to look at what happens to stock prices after a certain number
of consecutive up or down days. Let's say, for instance, I'd like to
see where a stock's price is 5 days after a run of 5 consecutive up
days.

I can't even begin to figure out the queries needed to accomplish
this. Maybe somebody can help me.

Here are my db fields:
symbol
date
adj_open (adj stands for adjusted, i.e. adjusted for splits)
adj_close

I know how to get the calculated field (adj_close - adj_open) to
determine whether it's an up or down day. In fact, I know how to do
many types of queries based on individual records, but I have no idea
how to do queries whose results are based on other records. For
instance, consider the following:
1.) I need to determine "consecutiveness" (forgive me for
inventing words). How to do that?
2.) After getting consecutiveness, I need to jump ahead 5 days
and look at price. How to do that?

Any ideas would be greatly appreciated!

Nelson

May 18 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 17 May 2007 17:42:07 -0700, Nelson <hu**********@yahoo.comwrote:

This very hard to do in SQL, because it is a set-based language, not a
procedural one. Processing records sequentially is not one of its
strong suits.
Come to the rescue: VBA programming with recordsets. Once you get the
hang of it it's pretty trivial to write code that can look for these
patterns as it is looping over an ordered set of records.

-Tom.
>Hi All,

I want to look at what happens to stock prices after a certain number
of consecutive up or down days. Let's say, for instance, I'd like to
see where a stock's price is 5 days after a run of 5 consecutive up
days.

I can't even begin to figure out the queries needed to accomplish
this. Maybe somebody can help me.

Here are my db fields:
symbol
date
adj_open (adj stands for adjusted, i.e. adjusted for splits)
adj_close

I know how to get the calculated field (adj_close - adj_open) to
determine whether it's an up or down day. In fact, I know how to do
many types of queries based on individual records, but I have no idea
how to do queries whose results are based on other records. For
instance, consider the following:
1.) I need to determine "consecutiveness" (forgive me for
inventing words). How to do that?
2.) After getting consecutiveness, I need to jump ahead 5 days
and look at price. How to do that?

Any ideas would be greatly appreciated!

Nelson
May 18 '07 #2

P: n/a
On May 18, 11:11 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On 17 May 2007 17:42:07 -0700, Nelson <huskies_b...@yahoo.comwrote:

This very hard to do in SQL, because it is a set-based language, not a
procedural one. Processing records sequentially is not one of its
strong suits.
Come to the rescue: VBA programming with recordsets. Once you get the
hang of it it's pretty trivial to write code that can look for these
patterns as it is looping over an ordered set of records.

-Tom.
Hi All,
I want to look at what happens to stock prices after a certain number
of consecutive up or down days. Let's say, for instance, I'd like to
see where a stock's price is 5 days after a run of 5 consecutive up
days.
I can't even begin to figure out the queries needed to accomplish
this. Maybe somebody can help me.
Here are my db fields:
symbol
date
adj_open (adj stands for adjusted, i.e. adjusted for splits)
adj_close
I know how to get the calculated field (adj_close - adj_open) to
determine whether it's an up or down day. In fact, I know how to do
many types of queries based on individual records, but I have no idea
how to do queries whose results are based on other records. For
instance, consider the following:
1.) I need to determine "consecutiveness" (forgive me for
inventing words). How to do that?
2.) After getting consecutiveness, I need to jump ahead 5 days
and look at price. How to do that?
Any ideas would be greatly appreciated!
Nelson- Hide quoted text -

- Show quoted text -
Thanks Tom!

Just knowing the right terminology to pursue the issue further is
worth its weight in gold. Although, when you think about it, words on
a computer screen don't weigh a helluva lot, do they? But I
digress...

I'll be sure to check out recordsets, and VBA in general.

Cheers.
Nelson

May 18 '07 #3

P: n/a
On 18 May 2007 06:13:57 -0700, Nelson <hu**********@yahoo.comwrote:

Good for you!
Keywords include: OpenRecordset, MoveNext, While Not rs.EOF, and
beyond that it's mostly a matter of keeping track of sequential days
of Up or Down using booleans or integer counters.

-Tom.

>On May 18, 11:11 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>On 17 May 2007 17:42:07 -0700, Nelson <huskies_b...@yahoo.comwrote:

This very hard to do in SQL, because it is a set-based language, not a
procedural one. Processing records sequentially is not one of its
strong suits.
Come to the rescue: VBA programming with recordsets. Once you get the
hang of it it's pretty trivial to write code that can look for these
patterns as it is looping over an ordered set of records.

-Tom.
>Hi All,
>I want to look at what happens to stock prices after a certain number
of consecutive up or down days. Let's say, for instance, I'd like to
see where a stock's price is 5 days after a run of 5 consecutive up
days.
>I can't even begin to figure out the queries needed to accomplish
this. Maybe somebody can help me.
>Here are my db fields:
symbol
date
adj_open (adj stands for adjusted, i.e. adjusted for splits)
adj_close
>I know how to get the calculated field (adj_close - adj_open) to
determine whether it's an up or down day. In fact, I know how to do
many types of queries based on individual records, but I have no idea
how to do queries whose results are based on other records. For
instance, consider the following:
1.) I need to determine "consecutiveness" (forgive me for
inventing words). How to do that?
2.) After getting consecutiveness, I need to jump ahead 5 days
and look at price. How to do that?
>Any ideas would be greatly appreciated!
>Nelson- Hide quoted text -

- Show quoted text -

Thanks Tom!

Just knowing the right terminology to pursue the issue further is
worth its weight in gold. Although, when you think about it, words on
a computer screen don't weigh a helluva lot, do they? But I
digress...

I'll be sure to check out recordsets, and VBA in general.

Cheers.
Nelson
May 18 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.