Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Loop Dates..... Urgent!

    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

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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.

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    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.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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_

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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.

  6. #6
    Join Date
    Dec 2009
    Posts
    1
    Quote Originally Posted by christomassey View Post
    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.

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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.

  8. #8
    Join Date
    Mar 2011
    Posts
    5

    Similar

    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

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What is your table structure (Post as CREATE TABLE)
    What is the actual statement that you are running?

    I'm running 1.12.2
    Again: that is not the PostgreSQL version.

  10. #10
    Join Date
    Mar 2011
    Posts
    5
    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

  11. #11
    Join Date
    Mar 2011
    Posts
    5
    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

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Helpseeker View Post
    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

  13. #13
    Join Date
    Mar 2011
    Posts
    5
    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

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Helpseeker View Post
    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)
    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

  15. #15
    Join Date
    Mar 2011
    Posts
    5
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •