Detecting when a futures contract is with the Bloomberg Excel plugin

[enterthe]

Hi,

I have a spreadsheet with livedata from the bloomberg excel plugin.

I'd like to work out when a futures contract is open or not. I don't want to base my calculations on the official open and close times as they aren't exact.

Does anyone know how to do this? I'm looking at the AEX futures and BBG is showing bids and offers even though the market is closed.

Cheers,

Enter

 

[TWI]

Ask bloomberg if they provide a field to indicate market state then pull this into excel.

 

[A Dashing Blade]

If you go to your Bloomberg installation directory you should find a BBFIELDS.TBL file

ie mine's in C:\Program Files\Bloomberg\Bloomberg_03\API\BBFIELDS.TBL

Open this with exceland it'll look like this . . .

10|Real Time Quotes|0| |0001|Last Price|LAST_PRICE|3998|27|3
10|Real Time Quotes|0| |0002|Bid Price|BID|3998|27|3
10|Real Time Quotes|0| |0003|Ask Price|ASK|3998|27|3
10|Real Time Quotes|0| |0007|Volume|VOLUME|2974|11|2
10|Real Time Quotes|0| |0009|High Price|HIGH|3998|11|3
10|Real Time Quotes|0| |000A|Low Price|LOW|3998|11|3
10|Real Time Quotes|0| |000B|Bid Yield|BID_YIELD|2440|27|2
10|Real Time Quotes|0| |000C|Ask Yield|ASK_YIELD|2440|27|2
10|Real Time Quotes|0| |0010|Open Interest (Realtime)|RT_OPEN_INTEREST|1542|14|2
10|Real Time Quotes|0| |001F|Best Bid|BEST_BID|3998|27|3
10|Real Time Quotes|0| |0020|Best Ask|BEST_ASK|3998|27|3
10|Real Time Quotes|0| |0023|Mid Price|MID|4062|27|3
10|Real Time Quotes|0| |0027|Last Trade|LAST_TRADE|3998|27|3
10|Real Time Quotes|0| |002D|Open Price|OPEN|3998|11|3

Basically this is a list of all available (incluing undocumented 😉 ) bbg commands that you can use via the =BLP() excel function or via the API.

A judicous bit of formatting in excel (Data, Text To Columns, Delimited) will get it in a more readable format.

I'd find something along the lines of a "time of last trade" or maybe this one "RT_EXCH_MARKET_STATUS - The status of the exchange that the requested security is running on." is worth investigating

Hope this helps, it's a hot Friday afternoon and I'm off to an early start leaving do so sorry I can't check it out further.