Looking for SPY weekly returns by day of the week

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.

 

Python pandas. Should be less than 10 lines

 

Ugh.

 

• 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.

 

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

 

Import the data into Excel and use its date functions to analyze any time frame you want.

 

Hey guys, thanks for this. I'm a newbie at coding but totally game. Can you baby-step by baby-step? I have the daily data in Excel.

 

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

 

Isn't nasdaqdatalink a paid service?