subset section of trading day from RBloomberg bar download

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

subset section of trading day from RBloomberg bar download

Tim Meggs
Hi R finance people,

I have downloaded some 15-min price Bar data across a number of days from Bloomberg for the March13 FTSE futures.

library(Rbbg)
conn <- blpConnect()
ftse <- "Z H3 Index"
ftse_Bars <- bar(conn, ftse, "TRADE", "2013-01-04 07:00:00.000", "2013-02-01 20:00:00.000", "15")
ftse_Bars<-as.xts(ftse_Bars)
blpDisconnect(conn)

The data looks like this:

                                           time   open   high    low  close numEvents volume
2013-01-10T01:00:00.000 2013-01-10T01:00:00.000 6050.5 6050.5 6047.0 6048.0        12     12
2013-01-10T01:15:00.000 2013-01-10T01:15:00.000 6046.5 6047.5 6046.5 6047.5         2      2
2013-01-10T01:30:00.000 2013-01-10T01:30:00.000 6046.5 6046.5 6044.5 6045.0        12     21
2013-01-10T01:45:00.000 2013-01-10T01:45:00.000 6044.5 6045.5 6044.5 6045.5         6      9
2013-01-10T02:00:00.000 2013-01-10T02:00:00.000 6045.5 6049.0 6045.5 6047.5        11     13
2013-01-10T02:15:00.000 2013-01-10T02:15:00.000 6053.0 6059.0 6049.0 6058.0        37     68

I would like to remove those bars that fall outside the hours 07:00 UTC to 15:30 UTC, to leave me with data just from the hours when the cash index is open.  Given the time column I receive from Bloomberg is in the slightly odd format format "%Y-%m-%dT%H:%M:%S" with the strange "T" in the middle how can subset out my desired data?

Any help greatly appreciated.

Thanks
Tim
Reply | Threaded
Open this post in threaded view
|

Re: subset section of trading day from RBloomberg bar download

Michael Weylandt


On Jul 25, 2013, at 14:40, Tim Meggs <[hidden email]> wrote:

> Hi R finance people,
>
> I have downloaded some 15-min price Bar data across a number of days from
> Bloomberg for the March13 FTSE futures.
>
> library(Rbbg)
> conn <- blpConnect()
> ftse <- "Z H3 Index"
> ftse_Bars <- bar(conn, ftse, "TRADE", "2013-01-04 07:00:00.000", "2013-02-01
> 20:00:00.000", "15")
> ftse_Bars<-as.xts(ftse_Bars)
> blpDisconnect(conn)
>
> The data looks like this:
>
>                                           time   open   high    low  close
> numEvents volume
> 2013-01-10T01:00:00.000 2013-01-10T01:00:00.000 6050.5 6050.5 6047.0 6048.0      
> 12     12
> 2013-01-10T01:15:00.000 2013-01-10T01:15:00.000 6046.5 6047.5 6046.5 6047.5        
> 2      2
> 2013-01-10T01:30:00.000 2013-01-10T01:30:00.000 6046.5 6046.5 6044.5 6045.0      
> 12     21
> 2013-01-10T01:45:00.000 2013-01-10T01:45:00.000 6044.5 6045.5 6044.5 6045.5        
> 6      9
> 2013-01-10T02:00:00.000 2013-01-10T02:00:00.000 6045.5 6049.0 6045.5 6047.5      
> 11     13
> 2013-01-10T02:15:00.000 2013-01-10T02:15:00.000 6053.0 6059.0 6049.0 6058.0      
> 37     68
>
> I would like to remove those bars that fall outside the hours 07:00 UTC to
> 15:30 UTC, to leave me with data just from the hours when the cash index is
> open.  Given the time column I receive from Bloomberg is in the slightly odd
> format format "%Y-%m-%dT%H:%M:%S" with the strange "T" in the middle how can
> subset out my desired data?

Assuming the as.xts call went properly, I believe you can just use the time-period form of ISO8601 subsetting. I'm not in a position to test, but I think it's

CC['07:00/15:30']

There might be a T in there. Check ?"[.xts" and ?xts for details.

Note that I'm assuming you got the as.xts call to work properly. If Bloomberg does put in that T, I'd have thought you'd need to pass an explicit format string.

Michael

>
> Any help greatly appreciated.
>
> Thanks
> Tim
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/subset-section-of-trading-day-from-RBloomberg-bar-download-tp4672338.html
> Sent from the Rmetrics mailing list archive at Nabble.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.

_______________________________________________
[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: subset section of trading day from RBloomberg bar download

Brian Rowe
In reply to this post by Tim Meggs
That strange 'T' happens to be specified as part of the ISO 8601 date and time standard. As such it is compatible with as.POSIXlt and as.POSIXct.

> as.POSIXlt('2013-01-10T00:00:00.000')
[1] "2013-01-10"

https://en.wikipedia.org/wiki/ISO_8601


On Jul 25, 2013, at 2:40 PM, Tim Meggs <[hidden email]> wrote:

> Hi R finance people,
>
> I have downloaded some 15-min price Bar data across a number of days from
> Bloomberg for the March13 FTSE futures.
>
> library(Rbbg)
> conn <- blpConnect()
> ftse <- "Z H3 Index"
> ftse_Bars <- bar(conn, ftse, "TRADE", "2013-01-04 07:00:00.000", "2013-02-01
> 20:00:00.000", "15")
> ftse_Bars<-as.xts(ftse_Bars)
> blpDisconnect(conn)
>
> The data looks like this:
>
>                                           time   open   high    low  close
> numEvents volume
> 2013-01-10T01:00:00.000 2013-01-10T01:00:00.000 6050.5 6050.5 6047.0 6048.0      
> 12     12
> 2013-01-10T01:15:00.000 2013-01-10T01:15:00.000 6046.5 6047.5 6046.5 6047.5        
> 2      2
> 2013-01-10T01:30:00.000 2013-01-10T01:30:00.000 6046.5 6046.5 6044.5 6045.0      
> 12     21
> 2013-01-10T01:45:00.000 2013-01-10T01:45:00.000 6044.5 6045.5 6044.5 6045.5        
> 6      9
> 2013-01-10T02:00:00.000 2013-01-10T02:00:00.000 6045.5 6049.0 6045.5 6047.5      
> 11     13
> 2013-01-10T02:15:00.000 2013-01-10T02:15:00.000 6053.0 6059.0 6049.0 6058.0      
> 37     68
>
> I would like to remove those bars that fall outside the hours 07:00 UTC to
> 15:30 UTC, to leave me with data just from the hours when the cash index is
> open.  Given the time column I receive from Bloomberg is in the slightly odd
> format format "%Y-%m-%dT%H:%M:%S" with the strange "T" in the middle how can
> subset out my desired data?
>
> Any help greatly appreciated.
>
> Thanks
> Tim
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/subset-section-of-trading-day-from-RBloomberg-bar-download-tp4672338.html
> Sent from the Rmetrics mailing list archive at Nabble.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.


        [[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: subset section of trading day from RBloomberg bar download

Tim Meggs
In reply to this post by Michael Weylandt
Thanks Michael.

I think I was too hasty in posting up my sample code, the as.xts() call was not supposed to be included. It was there because I had tried to convert to an xts object exactly so I could use the subsetting method you suggested. However, I don't think the conversion works (and hence I can't use that subset method) because the 'time' column returned in the xts object after running as.xts contains character strings and I don't think you can sunset easily on these.

I have a feeling I'm being a massive dullard about how to approach this, so please anyone feel free to point out the correct way for me to do this.

Thanks,
Tim
Reply | Threaded
Open this post in threaded view
|

Re: subset section of trading day from RBloomberg bar download

John Laing
Tim,

Rbbg returns bars as a data.frame, so to turn this into an XTS you need to
split out the time column. Something like this:

fbx <- xts(ftse_Bars[, c("open", "high", "low", "close", "numEvents",
"volume")], as.POSIXlt(ftse_Bars$time))

Then you should be able to use the subset method.

-John


On Thu, Jul 25, 2013 at 3:19 PM, Tim Meggs <[hidden email]> wrote:

> Thanks Michael.
>
> I think I was too hasty in posting up my sample code, the as.xts() call was
> not supposed to be included. It was there because I had tried to convert to
> an xts object exactly so I could use the subsetting method you suggested.
> However, I don't think the conversion works (and hence I can't use that
> subset method) because the 'time' column returned in the xts object after
> running as.xts contains character strings and I don't think you can sunset
> easily on these.
>
> I have a feeling I'm being a massive dullard about how to approach this, so
> please anyone feel free to point out the correct way for me to do this.
>
> Thanks,
> Tim
>
>
>
> --
> View this message in context:
> http://r.789695.n4.nabble.com/subset-section-of-trading-day-from-RBloomberg-bar-download-tp4672338p4672348.html
> Sent from the Rmetrics mailing list archive at Nabble.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.
>

        [[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: [SPAM] - Re: subset section of trading day from RBloombergbar download - Email found in subject

David Reiner
In reply to this post by Tim Meggs
Actually, the "T", while part of the standard, has to be specified in the format for as.POSIXct, or removed.
Then the xts command has to pass the core data and the index as POSIXct separately:

ftse.xts <- xts(ftse_Bars[,-1], as.POSIXct(sub("T"," ",ftse_Bars[,1])))

Then the subsetting can be done using Jeff's amazing datetime filtering:

ftse.sub <- ftse.xts["T07:00/T15:30"]

HTH,
David L. Reiner
XR Trading LLC

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Brian Rowe
Sent: Thursday, July 25, 2013 2:05 PM
To: Tim Meggs
Cc: [hidden email]
Subject: [SPAM] - Re: [R-SIG-Finance] subset section of trading day from RBloombergbar download - Email found in subject

That strange 'T' happens to be specified as part of the ISO 8601 date and time standard. As such it is compatible with as.POSIXlt and as.POSIXct.

> as.POSIXlt('2013-01-10T00:00:00.000')
[1] "2013-01-10"

https://en.wikipedia.org/wiki/ISO_8601


On Jul 25, 2013, at 2:40 PM, Tim Meggs <[hidden email]> wrote:

> Hi R finance people,
>
> I have downloaded some 15-min price Bar data across a number of days from
> Bloomberg for the March13 FTSE futures.
>
> library(Rbbg)
> conn <- blpConnect()
> ftse <- "Z H3 Index"
> ftse_Bars <- bar(conn, ftse, "TRADE", "2013-01-04 07:00:00.000", "2013-02-01
> 20:00:00.000", "15")
> ftse_Bars<-as.xts(ftse_Bars)
> blpDisconnect(conn)
>
> The data looks like this:
>
>                                           time   open   high    low  close
> numEvents volume
> 2013-01-10T01:00:00.000 2013-01-10T01:00:00.000 6050.5 6050.5 6047.0 6048.0
> 12     12
> 2013-01-10T01:15:00.000 2013-01-10T01:15:00.000 6046.5 6047.5 6046.5 6047.5
> 2      2
> 2013-01-10T01:30:00.000 2013-01-10T01:30:00.000 6046.5 6046.5 6044.5 6045.0
> 12     21
> 2013-01-10T01:45:00.000 2013-01-10T01:45:00.000 6044.5 6045.5 6044.5 6045.5
> 6      9
> 2013-01-10T02:00:00.000 2013-01-10T02:00:00.000 6045.5 6049.0 6045.5 6047.5
> 11     13
> 2013-01-10T02:15:00.000 2013-01-10T02:15:00.000 6053.0 6059.0 6049.0 6058.0
> 37     68
>
> I would like to remove those bars that fall outside the hours 07:00 UTC to
> 15:30 UTC, to leave me with data just from the hours when the cash index is
> open.  Given the time column I receive from Bloomberg is in the slightly odd
> format format "%Y-%m-%dT%H:%M:%S" with the strange "T" in the middle how can
> subset out my desired data?
>
> Any help greatly appreciated.
>
> Thanks
> Tim
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/subset-section-of-trading-day-from-RBloomberg-bar-download-tp4672338.html
> Sent from the Rmetrics mailing list archive at Nabble.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.


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


This e-mail and any materials attached hereto, including, without limitation, all content hereof and thereof (collectively, "XR Content") are confidential and proprietary to XR Trading, LLC ("XR") and/or its affiliates, and are protected by intellectual property laws.  Without the prior written consent of XR, the XR Content may not (i) be disclosed to any third party or (ii) be reproduced or otherwise used by anyone other than current employees of XR or its affiliates, on behalf of XR or its affiliates.

THE XR CONTENT IS PROVIDED AS IS, WITHOUT REPRESENTATIONS OR WARRANTIES OF ANY KIND.  TO THE MAXIMUM EXTENT PERMISSIBLE UNDER APPLICABLE LAW, XR HEREBY DISCLAIMS ANY AND ALL WARRANTIES, EXPRESS AND IMPLIED, RELATING TO THE XR CONTENT, AND NEITHER XR NOR ANY OF ITS AFFILIATES SHALL IN ANY EVENT BE LIABLE FOR ANY DAMAGES OF ANY NATURE WHATSOEVER, INCLUDING, BUT NOT LIMITED TO, DIRECT, INDIRECT, CONSEQUENTIAL, SPECIAL AND PUNITIVE DAMAGES, LOSS OF PROFITS AND TRADING LOSSES, RESULTING FROM ANY PERSON'S USE OR RELIANCE UPON, OR INABILITY TO USE, ANY XR CONTENT, EVEN IF XR IS ADVISED OF THE POSSIBILITY OF SUCH DAMAGES OR IF SUCH DAMAGES WERE FORESEEABLE.

_______________________________________________
[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: subset section of trading day from RBloomberg bar download

Jeffrey Ryan
In reply to this post by John Laing
And the xts subset method RMW refers to is:

CC['T07:00/T15:30']

i.e. you need the T

This isn't technically part of the standard at all, but the ability to
filter out parts of a day seemed a reasonable addition.  You can also look
at .indexhour() and .indexmin() and traditional equality ops.

HTH
Jeff


On Thu, Jul 25, 2013 at 2:22 PM, John Laing <[hidden email]> wrote:

> Tim,
>
> Rbbg returns bars as a data.frame, so to turn this into an XTS you need to
> split out the time column. Something like this:
>
> fbx <- xts(ftse_Bars[, c("open", "high", "low", "close", "numEvents",
> "volume")], as.POSIXlt(ftse_Bars$time))
>
> Then you should be able to use the subset method.
>
> -John
>
>
> On Thu, Jul 25, 2013 at 3:19 PM, Tim Meggs <[hidden email]> wrote:
>
> > Thanks Michael.
> >
> > I think I was too hasty in posting up my sample code, the as.xts() call
> was
> > not supposed to be included. It was there because I had tried to convert
> to
> > an xts object exactly so I could use the subsetting method you suggested.
> > However, I don't think the conversion works (and hence I can't use that
> > subset method) because the 'time' column returned in the xts object after
> > running as.xts contains character strings and I don't think you can
> sunset
> > easily on these.
> >
> > I have a feeling I'm being a massive dullard about how to approach this,
> so
> > please anyone feel free to point out the correct way for me to do this.
> >
> > Thanks,
> > Tim
> >
> >
> >
> > --
> > View this message in context:
> >
> http://r.789695.n4.nabble.com/subset-section-of-trading-day-from-RBloomberg-bar-download-tp4672338p4672348.html
> > Sent from the Rmetrics mailing list archive at Nabble.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.
> >
>
>         [[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.
>



--
Jeffrey Ryan
[hidden email]

www.lemnica.com

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