Loop Dates..... Urgent!

christomassey · #1 (**permalink**) 11-19-09, 18:14

Hi

I have a table with returns for different stocks on each day over a 3 year period. I want average the returns for each day, but only include the returns of stocks who traded today (t) and yesterday (t-1).

as such i have the following query

select date_, avg(ret)
from (select *
from final.lrg, (select one.symbol
from (select symbol
from final.lrg
where date_ = '2005-01-03') as one, -- stocks that traded on t-1

(select symbol
from final.lrg
where date_ = '2005-01-04' ) as two -- stocks that traded on t

where one.symbol = two.symbol) as three -- stocks that traded on both t and t-1

where final.lrg.symbol = three.symbol and final.lrg.date_ = '2005-01-04') as four -- get average return for day t
group by date_

this query gives me the right answer, however i need to create a loop that will run the same query for all the days

any help will be much appreciated

artacus72 · #2 (**permalink**) 11-19-09, 19:25

Save the "urgent" for when you are paying for support. Here we're all volunteering.
Two, put your code in a code block.
Three, tell us the version of Postgres you are using.
Four... well I'll see if I can determine your schema from provided query but it helps to provide table structure.

Biggest issue is which version you are running.

christomassey · #3 (**permalink**) 11-19-09, 19:32

Sorry.. I had no idea this is my first time asking for help on a thread

Im using Postgres 1.10.0

the schema is "final" with table name "lrg"
the table has columns like
"date_" the day of trade
"symbol" the identifier of each stock
"ret" is the return the stock made on that particular day

is there anything else?

i really appreciate your effort.

artacus72 · #4 (**permalink**) 11-19-09, 19:38

You were making things too hard

Code:

SELECT t.symbol, t.date_, AVG(ret) AS avg_ret
FROM final.lrg t
JOIN final.lrg t_1 ON t.date_ = t_1.date_ - 1
  AND t.symbol = t_1.symbol
WHERE t.date_ > current_date - INTERVAL '3 years'
GROUP BY t.symbol, t.date_

artacus72 · #5 (**permalink**) 11-19-09, 19:41

Quote:

Im using Postgres 1.10.0

No that is the version of pgAdmin not Postgres. That version belongs to Postgres 8.4. But it didn't matter because after I dug in to it, it was straight forward.

jezzicaz789 · #6 (**permalink**) 12-07-09, 04:24

Quote:

Originally Posted by christomassey

Hi

I have a table with returns for different stocks on each day over a 3 year period. I want average the returns for each day, but only include the returns of stocks who traded today (t) and yesterday (t-1).

as such i have the following query

select date_, avg(ret)
from (select *
from final.lrg, (select one.symbol
from (select symbol
from final.lrg
where date_ = '2005-01-03') as one, -- stocks that traded on t-1

(select symbol
from final.lrg
where date_ = '2005-01-04' ) as two -- stocks that traded on t

where one.symbol = two.symbol) as three -- stocks that traded on both t and t-1

where final.lrg.symbol = three.symbol and final.lrg.date_ = '2005-01-04') as four -- get average return for day t
group by date_

this query gives me the right answer, however i need to create a loop that will run the same query for all the days

any help will be much appreciated

Thanks you for the post.
Hi guys, Im a newbie. Nice to join this forum.

artacus72 · #7 (**permalink**) 12-07-09, 12:14

Grrr, stupid spammers. I swear dbForums gets more spam than any forum I've been on. At least they could give us a button to report it.

Helpseeker · #8 (**permalink**) 03-21-11, 17:48

Hi I have a similar problem as user 'christomassey'
so i was really pleased to see a solution to the problem.

However I tried the solution, and it seems to work except that it deletes every monday in my sample. I think its doing this because there obviously is no data for sat. and sun. as my data is only for business days.

How would I adjust the loop above so that only stocks who trade on day t and on day t-1 ( so a stock who traded on monday and the previous friday would need to be included on the monday) are included.

the sample length spans 2005 through 2007

I'm running 1.12.2
schema = public
table = filtered

Reply would be much appreciated
thanks

shammat · #9 (**permalink**) 03-21-11, 17:53

What is your table structure (Post as CREATE TABLE)
What is the actual statement that you are running?

Quote:

I'm running 1.12.2

Again: that is not the PostgreSQL version.

Helpseeker · #10 (**permalink**) 03-21-11, 18:24

this is the table that i am trying to use the query on

CREATE TABLE filtered
(
date_ date,
ticker character varying,
symbol character varying,
idcom numeric,
shrcode numeric,
sic numeric,
adjprc numeric,
ret numeric,
adjshr numeric,
adjvol numeric,
cap numeric,
cape numeric,
ssize numeric
)
WITH (
OIDS=FALSE

im using postgresql 9.0
and here is the query

SELECT t.date_, t.ticker, t.symbol, t.idcom, t.shrcode, t.sic, t.adjprc, t.ret, t.adjshr,t.adjvol, t.cap, t.cape, t.ssize
FROM filtered t
JOIN filtered t_1 ON t.date_ = t_1.date_ -1
AND t.symbol = t_1.symbol
WHERE t.date_ > current_date - INTERVAL '7 years'
order by symbol, date_

thnak you

Helpseeker · #11 (**permalink**) 03-21-11, 19:12

if this helps, a friend tried to help me however they were familiar with MSsql and not postgresql. they gave me this code, but it wont work in postgresql

DECLARE day_cur CURSOR FOR
SELECT DISTINCT date_ FROM filtered ORDER BY date_;
FETCH next from day_cur INTO @day
WHILE (@@FETCH_STATUS = 0)
BEGIN
select date_, avg(ret)
from (select *
from filtered, (select one.symbol
from (select symbol
from filtered
where date_ = '2005-01-03') as one, -- stocks that traded on t-1

(select symbol
from filtered
where date_ = '2005-01-04' ) as two -- stocks that traded on t

where one.symbol = two.symbol) as three -- stocks that traded on both t and t-1

where filtered.symbol = three.symbol and filtered.date_ = '2005-01-04') as four -- get return for day t
group by date_

FETCH NEXT FROM day_cur INTO @day
END
CLOSE day_cur
DEALLOCATE day_cur

thank you

shammat · #12 (**permalink**) 03-22-11, 05:13

Quote:

Originally Posted by Helpseeker

SELECT t.date_, t.ticker, t.symbol, t.idcom, t.shrcode, t.sic, t.adjprc, t.ret, t.adjshr,t.adjvol, t.cap, t.cape, t.ssize
FROM filtered t
JOIN filtered t_1 ON t.date_ = t_1.date_ -1
AND t.symbol = t_1.symbol
WHERE t.date_ > current_date - INTERVAL '7 years'
order by symbol, date_

And what exactly should that query do?
What error do you get?
Show us some sample data (as INSERT INTO) and the expected result based on that sample data

And please use [code] tags to format your SQL code in the future

Helpseeker · #13 (**permalink**) 03-22-11, 06:14

Hi I am quite a novice at sql so please forgive me for any inconvenience.

I would like a query that would go through a table and select each row based on wether a stock has traded on two consecutive days, i.e on day t and t-1.
therfore for a stock to be selected on a wednesday it would need to have traded on the tuesday aswell and so on.

here is a basic sample of the data

date symbol price
01jan hgf 3.00
02jan hgf 3.01
04jan hgf 2.99
05jan hgf 3.00
07jan hgf 3.01
08jan hgf 3.04
10jan hgf 2.99

the query would need to retrieve the following rows, as they have trades in the day prior.

date symbol price
02jan hgf 3.01
05jan hgf 3.00
08jan hgf 3.04

I think the sql code that I have does work but it does not account for the fact that the day preceding a Monday is not a Sunday but a Friday ( the data is Monday through Friday).
If a stock traded on a Friday and then on the Monday, the Monday trade would need to be included. However currently none of my Monday trades are included. I think this is because Postgresql thinks that Mondays are not valid as there are no trades on Sunday.

I don't know what [code] tags are sorry.

Thank you

shammat · #14 (**permalink**) 03-22-11, 06:52

Quote:

Originally Posted by Helpseeker

I would like a query that would go through a table and select each row based on wether a stock has traded on two consecutive days, i.e on day t and t-1.
therfore for a stock to be selected on a wednesday it would need to have traded on the tuesday aswell and so on.

Code:

SELECT * 
FROM ( 
  SELECT date_, 
         symbol, 
         price,
         lag(date_) over (partition by symbol order by date_) as prev_date,
  FROM the_table_with_no_name
) t
WHERE (date_ - prev_date = 1) 
  OR  (extract(isodow from prev_date) = 1 AND extract(isodow from date_) = 5)

Quote:

I don't know what [code] tags are sorry

This is used to properly format code and make it readable by using a fixed-width font.

To post the above formatted SQL I wrote the following

[code]
SELECT *
FROM (
...
) ....
[/code]

More details here:
http://www.vbulletin.com/forum/misc.php?do=bbcode

Helpseeker · #15 (**permalink**) 03-22-11, 08:03

Thank you for your effort.

I have run the code like this:

Code:

SELECT * 
FROM ( 
  SELECT date_, 
	 ticker,
         symbol,
         idcom,
         shrcode,
         sic,
         adjprc,
         ret,
         adjshr,
         adjvol,
         cap,
         cape,
         ssize,
         lag(date_) over (partition by symbol order by date_) as prev_date
  FROM public.filtered
) t
WHERE (date_ - prev_date = 1) 
  OR  (extract(isodow from prev_date) = 1 AND extract(isodow from date_) = 5);

it seems to work well in the middle of the week, but it it still cuts out mondays.

regards

Internet Services

Web Development

Digital Marketing

Consumer Tech