aligning dates from different sources

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

aligning dates from different sources

Eric Thungstom
I've downloaded some FRED data (ICSA) using quantmod and I'm trying to
align it with weekly GSPC data. I'm running into trouble because the ICSA
data seems to come out on Saturdays and weekly closes of GSPC are usually
on Friday. I tried a simple fix of subtracting 1 from the ICSA dates but I
still run into problems around holidays.  For example, the market was
closed on April 6, 2012 so I can't align the data for that week.

Any suggestions how I get the dates lined up so I can do some further
analysis ?  I know I could use a brute force method of doing a simple cbind
(combined <-cbind(sp,ICSA[,1]))  instead of merge but wondering if there's
a better way that takes holidays into account.

require(quantmod)
getSymbols('ICSA', src='FRED')
getSymbols('^GSPC', from='1967-01-01', to=Sys.Date())
index(ICSA) <-index(ICSA)-1
sp <-to.weekly(GSPC)[,6]
names(sp) <-'GSPC'
combined <-merge(ICSA,sp)
tail(combined, 15)


            ICSA    GSPC
2012-03-30 362000 1408.47
2012-04-05     NA 1398.08
2012-04-06 388000      NA
2012-04-13 389000 1370.26
2012-04-20 392000 1378.53
2012-04-27 368000 1403.36
2012-05-04 370000 1369.10
2012-05-11 372000 1353.39
2012-05-18 373000 1295.22
2012-05-25 389000 1317.82
2012-06-01 380000 1278.04
2012-06-08 389000 1325.66
2012-06-15 392000 1342.84
2012-06-22 386000 1335.02

        [[alternative HTML version deleted]]

_______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-sig-finance
-- Subscriber-posting only. If you want to post, subscribe first.
-- Also note that this is not the r-help list where general R questions should go.
Reply | Threaded
Open this post in threaded view
|

Re: aligning dates from different sources

Joshua Ulrich
On Sat, Jun 30, 2012 at 1:21 PM, Eric Thungstom
<[hidden email]> wrote:

> I've downloaded some FRED data (ICSA) using quantmod and I'm trying to
> align it with weekly GSPC data. I'm running into trouble because the ICSA
> data seems to come out on Saturdays and weekly closes of GSPC are usually
> on Friday. I tried a simple fix of subtracting 1 from the ICSA dates but I
> still run into problems around holidays.  For example, the market was
> closed on April 6, 2012 so I can't align the data for that week.
>
> Any suggestions how I get the dates lined up so I can do some further
> analysis ?  I know I could use a brute force method of doing a simple cbind
> (combined <-cbind(sp,ICSA[,1]))  instead of merge but wondering if there's
> a better way that takes holidays into account.
>
cbind.xts simply calls merge.xts, so I'm not sure why you would expect
different results.

You could merge the two raw series, use na.locf to fill in the missing
values, then use apply.weekly to get the last value for each week:

x <- merge(ICSA,Ad(GSPC))
x <- na.locf(x)
y <- apply.weekly(x, last)

> require(quantmod)
> getSymbols('ICSA', src='FRED')
> getSymbols('^GSPC', from='1967-01-01', to=Sys.Date())
> index(ICSA) <-index(ICSA)-1
> sp <-to.weekly(GSPC)[,6]
> names(sp) <-'GSPC'
> combined <-merge(ICSA,sp)
> tail(combined, 15)
<snip>

Best,
--
Joshua Ulrich  |  FOSS Trading: www.fosstrading.com

_______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-sig-finance
-- Subscriber-posting only. If you want to post, subscribe first.
-- Also note that this is not the r-help list where general R questions should go.
Reply | Threaded
Open this post in threaded view
|

Re: aligning dates from different sources

Jeffrey Ryan-2
Or you could simply change the index explicitly:

> indexFormat(ICSA) <- "%a"
> head(ICSA)
      ICSA
Sat 208000
Sat 207000
Sat 217000
Sat 204000
Sat 216000
Sat 229000
> index(ICSA) <- as.Date(index(ICSA))-1
> head(ICSA)
      ICSA
Fri 208000
Fri 207000
Fri 217000
Fri 204000
Fri 216000
Fri 229000
> indexFormat(ICSA) <- NULL  # use the default
> head(ICSA)
             ICSA
1967-01-06 208000
1967-01-13 207000
1967-01-20 217000
1967-01-27 204000
1967-02-03 216000
1967-02-10 229000
>

HTH
Jeff



On Sat, Jun 30, 2012 at 1:39 PM, Joshua Ulrich <[hidden email]> wrote:

> On Sat, Jun 30, 2012 at 1:21 PM, Eric Thungstom
> <[hidden email]> wrote:
>> I've downloaded some FRED data (ICSA) using quantmod and I'm trying to
>> align it with weekly GSPC data. I'm running into trouble because the ICSA
>> data seems to come out on Saturdays and weekly closes of GSPC are usually
>> on Friday. I tried a simple fix of subtracting 1 from the ICSA dates but I
>> still run into problems around holidays.  For example, the market was
>> closed on April 6, 2012 so I can't align the data for that week.
>>
>> Any suggestions how I get the dates lined up so I can do some further
>> analysis ?  I know I could use a brute force method of doing a simple cbind
>> (combined <-cbind(sp,ICSA[,1]))  instead of merge but wondering if there's
>> a better way that takes holidays into account.
>>
> cbind.xts simply calls merge.xts, so I'm not sure why you would expect
> different results.
>
> You could merge the two raw series, use na.locf to fill in the missing
> values, then use apply.weekly to get the last value for each week:
>
> x <- merge(ICSA,Ad(GSPC))
> x <- na.locf(x)
> y <- apply.weekly(x, last)
>
>> require(quantmod)
>> getSymbols('ICSA', src='FRED')
>> getSymbols('^GSPC', from='1967-01-01', to=Sys.Date())
>> index(ICSA) <-index(ICSA)-1
>> sp <-to.weekly(GSPC)[,6]
>> names(sp) <-'GSPC'
>> combined <-merge(ICSA,sp)
>> tail(combined, 15)
> <snip>
>
> Best,
> --
> Joshua Ulrich  |  FOSS Trading: www.fosstrading.com
>
> _______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions should go.



--
Jeffrey Ryan
[hidden email]

www.lemnica.com
www.esotericR.com

_______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-sig-finance
-- Subscriber-posting only. If you want to post, subscribe first.
-- Also note that this is not the r-help list where general R questions should go.
Reply | Threaded
Open this post in threaded view
|

Re: aligning dates from different sources

FJ M
In reply to this post by Eric Thungstom

The following link explains the rules for closing the exchange either for a full day or early. Also detailed are early closes and the reason going back historically.
 
http://www.nyse.com/pdfs/closings.pdf
 
The NYSE holiday schedule for 2012, 2013, 2014 is here:
 
http://www.nyx.com/en/holidays-and-hours/nyse
 
>From this you can see that 4/6/2012 was Good Friday and that NYSE will close for Good Friday on 3/29/2013 and 4/18/2014.
 
I have some of these in a file. If I'm in error on any dates, let me know and I will fix. I'm not sure if I'm allowed to post a file to this list. Following are the dates I have for from 12/31/1999(serial date, reason, date):
 
36525,New_Year's_Eve,12/31/1999
36710,Day_before_Independence_Day,7/3/2000
36854,Day_after_Thanksgiving_Day,11/24/2000
37050,Computer_malfunction,6/8/2001
37075,Day_before_Independence_Day,7/3/2001
37145,Closed_WTC_attack,9/11/2001
37146,Closed_WTC_attack,9/12/2001
37147,Closed_WTC_attack,9/13/2001
37148,Closed_WTC_attack,9/14/2001
37218,Day_after_Thanksgiving_Day,11/23/2001
37249,Christmas_Eve,12/24/2001
37442,Day_after_Independence_Day,7/5/2002
37510,Delayed_opening_WTC_anniversary,9/11/2002
37589,Day_after_Thanksgiving_Day,11/29/2002
37614,Christmas_Eve,12/24/2002
37805,Day_before_Independence_Day,7/3/2003
37953,Day_after_Thanksgiving_Day,11/28/2003
37979,Christmas_Eve,12/24/2003
37981,Friday_after_Christmas,12/26/2003
38149,National_Day_of_Mourning_President_Reagan,6/11/2004
38317,Day_after_Thanksgiving_Day,11/26/2004
38681,Day_after_Thanksgiving_Day,11/25/2005
38901,Day_before_Independence_Day,7/3/2006
39045,Day_after_Thanksgiving_Day,11/24/2006
39084,National_Day_of_Mourning_President_Ford,1/2/2007
39266,Day_before_Independence_Day,7/3/2007
39409,Day_after_Thanksgiving_Day,11/23/2007
39440,Christmas_Eve,12/24/2007
39632,Day_before_Independence_Day,7/3/2008
39780,Day_after_Thanksgiving_Day,11/28/2008
39806,Christmas_Eve,12/24/2008
40144,Day_after_Thanksgiving_Day,11/27/2009
40171,Christmas_Eve,12/24/2009
40508,Day_after_Thanksgiving_Day,11/26/2010
40535,Christmas_Eve,12/23/2010
40872,Day_after_Thanksgiving_Day,11/25/2011
41093,Day_before_Independence_Day,7/3/2012
41236,Day_after_Thanksgiving_Day,11/23/2012
41267,Christmas_Eve,12/24/2012
41458,Day_before_Independence_Day,7/3/2013
41607,Day_after_Thanksgiving_Day,11/29/2013
41632,Christmas_Eve,12/24/2013
41823,Day_before_Independence_Day,7/3/2014
41971,Day_after_Thanksgiving_Day,11/28/2014
41997,Christmas_Eve,12/24/2014


 

> Date: Sat, 30 Jun 2012 14:21:03 -0400
> From: [hidden email]
> To: [hidden email]
> Subject: [R-SIG-Finance] aligning dates from different sources
>
> I've downloaded some FRED data (ICSA) using quantmod and I'm trying to
> align it with weekly GSPC data. I'm running into trouble because the ICSA
> data seems to come out on Saturdays and weekly closes of GSPC are usually
> on Friday. I tried a simple fix of subtracting 1 from the ICSA dates but I
> still run into problems around holidays. For example, the market was
> closed on April 6, 2012 so I can't align the data for that week.
>
> Any suggestions how I get the dates lined up so I can do some further
> analysis ? I know I could use a brute force method of doing a simple cbind
> (combined <-cbind(sp,ICSA[,1])) instead of merge but wondering if there's
> a better way that takes holidays into account.
>
> require(quantmod)
> getSymbols('ICSA', src='FRED')
> getSymbols('^GSPC', from='1967-01-01', to=Sys.Date())
> index(ICSA) <-index(ICSA)-1
> sp <-to.weekly(GSPC)[,6]
> names(sp) <-'GSPC'
> combined <-merge(ICSA,sp)
> tail(combined, 15)
>
>
> ICSA GSPC
> 2012-03-30 362000 1408.47
> 2012-04-05 NA 1398.08
> 2012-04-06 388000 NA
> 2012-04-13 389000 1370.26
> 2012-04-20 392000 1378.53
> 2012-04-27 368000 1403.36
> 2012-05-04 370000 1369.10
> 2012-05-11 372000 1353.39
> 2012-05-18 373000 1295.22
> 2012-05-25 389000 1317.82
> 2012-06-01 380000 1278.04
> 2012-06-08 389000 1325.66
> 2012-06-15 392000 1342.84
> 2012-06-22 386000 1335.02
>
> [[alternative HTML version deleted]]
>
> _______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only. If you want to post, subscribe first.
> -- Also note that this is not the r-help list where general R questions should go.
     
        [[alternative HTML version deleted]]

_______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-sig-finance
-- Subscriber-posting only. If you want to post, subscribe first.
-- Also note that this is not the r-help list where general R questions should go.