Looking for SPY weekly returns by day of the week

S

SoCalOptionsWriter

I am trying to see is how many times the SP 500 scored a 8% or better return from a Monday open to Friday close, compared with a Tuesday open to Monday close, a Wednesday open to Tuesday close, a Thursday open to Wednesday close and a Friday open to Thursday close. Yes, I could download the historical data from Yahoo and work it out but if someone already has this or knows a faster way, I'd rather not reinvent the wheel. Thanks.
 
SoCalOptionsWriter said:
......Yes, I could download the historical data from Yahoo and work it out but if someone already has this or knows a faster way, I'd rather not reinvent the wheel. Thanks.
More...


  • You are not reinventing the wheel.
  • Most likely your requirements will not be met in any code you find.
  • This is also a good way to practice coding.
  • I recommend Python and output to a PDF file.
  • You will also have to take into account when the market is closed.
 
SoCalOptionsWriter said:
I am trying to see is how many times the SP 500 scored a 8% or better return from a Monday open to Friday close, compared with a Tuesday open to Monday close...
More...

Code: 
SQL> WITH w1 AS (SELECT SDate,O,C FROM mvConsolIndices WHERE Symbol = 'SPX')
  2  SELECT to_char(SDate,'D') AS DayOfWeek,count(*) AS Count8PCPlus
  3  FROM
  4  (SELECT t1.SDate,t1.O,t2.C AS C2,ln(t2.C/t1.O) AS RTN
  5    FROM w1 t1 LEFT JOIN w1 t2 ON bizDaysDiff(t1.SDate,t2.SDate) = 4
  6  ) WHERE C2 IS NOT NULL AND RTN >= .08
  7  GROUP BY to_char(SDate,'D') ORDER BY to_number(to_char(SDate,'D'));

DAYOFWEEK,COUNT8PCPLUS
2  ,  7
3  ,  11
4  ,  4
5  ,  7
6  ,  6

Elapsed: 00:00:00.49
 
Last edited:
Oh, one possible complication I see in the data, is that to just take every fifth line of data won't give me a weekly figure, as some days there is no trading because of a holiday or some shut down. Work might be needed, no?
 
Code: 
import pandas as pd
import nasdaqdatalink

k='gfgffglkkj5'
data = nasdaqdatalink.get('NSE/OIL',api_key=k)
data[data.index.weekday.isin([ 0,1])].resample('W').apply(diff)
once you have the data you get the weekdays you want and subtract. pandas provides a resample() to make it easier
 
2rosy said:
Code: 
import pandas as pd
import nasdaqdatalink

k='gfgffglkkj5'
data = nasdaqdatalink.get('NSE/OIL',api_key=k)
data[data.index.weekday.isin([ 0,1])].resample('W').apply(diff)
once you have the data you get the weekdays you want and subtract. pandas provides a resample() to make it easier
More...
Isn't nasdaqdatalink a paid service?
 
You must log in or register to reply here.
Back
Top