Mismatching Intraday Data

[kchowong]

Hope this finds everyone well.

I'm attempting to query intraday trade data specific to the microsecond of a date for an asset. (Ex. the 10:00:00 AM EST trade data on 07 Nov 2016 for USDCAD Curncy)

Via the Excel Bloomberg API, I am able to retrieve trade data for the example described telling me:

USDCAD Curncy @ 10:00:00 EST on 7 Nov 2016 to be 1.3378

The equivalent exercise via tia however:

import datetime as dt
from tia.bbg import LocalTerminal
import pandas as pd 

start_date = end_date = dt.datetime(year=2016, month=11, day=7, hour=10, minute=00, second=00, microsecond=00)
resp = LocalTerminal.get_intraday_tick("USDCAD Curncy","TRADE", start_date, end_date, include_condition_codes=True).as_frame()

Out[158]: size time type value 0 0 2016-11-07 10:00:00 TRADE 1.3399 1 0 2016-11-07 10:00:00 TRADE 1.3399 2 0 2016-11-07 10:00:00 TRADE 1.3399 3 0 2016-11-07 10:00:00 TRADE 1.3399 4 0 2016-11-07 10:00:00 TRADE 1.3399 5 0 2016-11-07 10:00:00 TRADE 1.3399 6 0 2016-11-07 10:00:00 TRADE 1.3399 7 0 2016-11-07 10:00:00 TRADE 1.3399 8 0 2016-11-07 10:00:00 TRADE 1.3399 9 0 2016-11-07 10:00:00 TRADE 1.3399 10 0 2016-11-07 10:00:00 TRADE 1.3399

Please note how USDCAD Curncy from tia is quoting 1.3399, which is materially diffferent from 1.3378 from excel.

Has anyone experienced similar issues?

I currently reside in the EST, where daylight saving time can potentially play a factor however through my tests, of adjusting the queried time to tia by +/- 1 hour still fails to align with the results i see in Excel/Bloomberg.

Tia Vs. Excel summarised comparison:

9:00:00 tia: -> 1.3398 excel:-> 1.3388

10:00:00 tia: -> 1.3399 excel: -> 1.3378

11:00:00 tia: -> 1.3405 excel: -> 1.34

Any help or insight would be very much appreciated. Thanks in advance!

[STguerin]

the bloomberg api return UTC timestamp. you can localize the timestamp and then convert to east if you want.

start_date = end_date = dt.datetime(year=2016, month=11, day=7, hour=13, minute=00, second=00, microsecond=00)
resp = LocalTerminal.get_intraday_tick("USDCAD Curncy","TRADE", start_date, end_date, include_condition_codes=True).as_frame()

resp = resp.set_index('time')
resp = resp.tz_localize('UTC')
resp = resp.tz_convert('America/New_York')

output:
                           size   type   value
time                                          
2016-11-07 10:00:00-05:00     0  TRADE  1.3378
2016-11-07 10:00:00-05:00     0  TRADE  1.3378
2016-11-07 10:00:00-05:00     0  TRADE  1.3378
2016-11-07 10:00:00-05:00     0  TRADE  1.3378
2016-11-07 10:00:00-05:00     0  TRADE  1.3378
2016-11-07 10:00:00-05:00     0  TRADE  1.3378

I called for 13:00:00 since UTC is east -5:00hours

you will have 1.3378 that way

[frankrao56]

Are there any adjustments necessary to make this work for equities? The USDCAD example above works fine for me. When I try it with AAPL US EQUITY (for example) I get an empty DataFrame. Thanks