{"text_format":"3","workbook":"xlsx","n_sheets":8,"sheets":["Title","Terminology","BQL.Date","BQL.Params","BQL.Expr","BQL.List","BQL.Query Examples","BQL Examples"],"not_rendered":{"defined_names":8},"read":"whole","date_system":"1900"}

{"sheet":"Title","index":0,"n_rows":8,"n_cols":1,"rows_kept":8,"merged":[[1,0,1,1],[2,0,2,1],[3,0,3,1],[5,0,5,1],[6,0,6,1],[7,0,7,1]]}
[]
["BQL Excel Examples"]
["BQL stands for Bloomberg Query Language, which is the language used to perform analytics with and retrieve data from Bloomberg."]
["This workbook contains several examples of the core functions for processing BQL queries, as well as supporting functions."]
[]
["Montgomery Investment Technology, Inc."]
["www.fintools.com"]
["Publication Date: November 1, 2019"]

{"sheet":"Terminology","index":1,"n_rows":7,"n_cols":4,"rows_kept":7,"merged":[[2,0,2,3]]}
["BQL Terminology"]
[]
["Terminology"]
["Example","Universe","Expression","Parameters"]
["Last price of IBM as of 12/13/2017","IBM US EQUITY","PX_LAST","DATES=2017-12-13"]
["Average last prices of IBM and AAPL in 2017","['IBM US EQUITY', 'AAPL US EQUITY']","avg(PX_LAST)","DATES=RANGE(2017-01-01,2017-12-31)"]
["The highest open interest on SPX options between 1/1/2017 and 1/15/2017","options('SPX INDEX')","max(group(open_int))","START_DATE=2017-01-31,END_DATE=2017-01-31"]

{"sheet":"BQL.Date","index":2,"n_rows":7,"n_cols":2,"rows_kept":7,"merged":[[2,0,2,1]]}
["BQL.Date"]
[]
["BQL.Date"]
["Input","Output"]
["2017-07-25",{"$f":"_xll.BQL.Date(A5)","v":"2017-07-25"}]
["2017-07-25",{"$f":"_xll.BQL.Date(A6)","v":"2017-07-25"}]
["2017-07-25",{"$f":"_xll.BQL.Date(A7)","v":"2017-07-25"}]

{"sheet":"BQL.Params","index":3,"n_rows":6,"n_cols":2,"rows_kept":6,"merged":[[2,0,2,1],[4,1,5,1]]}
["BQL.Params"]
[]
["BQL.Params"]
["Input","Output"]
["DATES",{"$f":"_xll.BQL.Params(A5,A6)","v":"DATES=2017-12-13"}]
[{"$f":"\"2017-12-13\"","v":"2017-12-13"}]

{"sheet":"BQL.Expr","index":4,"n_rows":6,"n_cols":2,"rows_kept":6,"merged":[[2,0,2,1],[4,1,5,1]]}
["BQL.Expr"]
[]
["BQL.Expr"]
["Input","Output"]
["max",{"$f":"_xll.BQL.Expr(A5:A6)","v":"max(PX_LAST)"}]
["PX_LAST"]

{"sheet":"BQL.List","index":5,"n_rows":7,"n_cols":2,"rows_kept":7,"merged":[[2,0,2,1],[4,1,6,1]]}
["BQL.List"]
[]
["BQL.List"]
["Input","Output"]
["IBM US EQUITY",{"$f":"_xll.BQL.List(A5:A7)","v":"['IBM US EQUITY','GE US EQUITY','MSFT US EQUITY']"}]
["GE US EQUITY"]
["MSFT US EQUITY"]

{"sheet":"BQL.Query Examples","index":6,"n_rows":10,"n_cols":3,"rows_kept":10,"merged":[[2,0,2,2]]}
["BQL.Query Examples"]
[]
["BQL.Query Examples"]
["Example","Input","Output"]
["Closing price of IBM on 12/13/2017","BQL.Query(\"get(PX_LAST) for('IBM US EQUITY') with(DATES=2017-12-13)\")",{"$f":"_xll.BQL.Query(\"get(PX_LAST) for('IBM US EQUITY') with(DATES=2017-12-13)\")","v":153.91}]
["Closing price of IBM on 12/13/2017, rounded to 4 decimals","BQL.Query(\"get(round(px_last,4)) for('IBM US EQUITY') with(DATES=2017-12-13)\")",{"$f":"_xll.BQL.Query(\"get(round(px_last,4)) for('IBM US EQUITY') with(DATES=2017-12-13)\")","v":153.91}]
["Closing price of IBM on 12/13/2017, rounded to 4 decimals, using a cell-referenced date","BQL.Query(\"get(round(px_last,4)) for('IBM US EQUITY') with(\"&BQL.Params(\"DATES\",\"2017-12-13\")&\")\")",{"$f":"_xll.BQL.Query(\"get(round(px_last,4)) for('IBM US EQUITY') with(\"&_xll.BQL.Params(\"DATES\",BQL.Params!A6)&\")\")","v":153.91}]
["Average daily closing prices of IBM for 2017","BQL.Query(\"get(avg(PX_LAST)) for('IBM US EQUITY') with(DATES=RANGE(2017-01-01, 2017-12-31))\")",{"$f":"_xll.BQL.Query(\"get(avg(PX_LAST)) for('IBM US EQUITY') with(DATES=RANGE(2017-01-01, 2017-12-31))\")","v":{"v":157.65800796812752,"d":"157.66"}}]
["Average daily closing prices (rounded to 4 decimals) of IBM for 2017, excluding #N/A's","BQL.Query(\"get(avg(round(dropna(PX_LAST),4))) for('IBM US EQUITY') with(DATES=RANGE(2017-01-01,2017-12-31))\")",{"$f":"_xll.BQL.Query(\"get(avg(round(dropna(PX_LAST),4))) for('IBM US EQUITY') with(DATES=RANGE(2017-01-01,2017-12-31))\")","v":{"v":157.65800796812752,"d":"157.66"}}]
["IBM's average close price during December divided by its average close price during January minus one","BQL.Query(\"let(#StartAvg = avg(PX_LAST(START=2017-01-01,END=2017-01-31)); #EndAvg = avg(PX_LAST(START=2017-12-01,END=2017-12-31)); #Return = #EndAvg / #StartAvg - 1;) get(#Return) for('IBM US EQUITY')\")",{"$f":"_xll.BQL.Query(\"let(#StartAvg = avg(PX_LAST(START=2017-01-01,END=2017-01-31)); #EndAvg = avg(PX_LAST(START=2017-12-01,END=2017-12-31)); #Return = #EndAvg / #StartAvg - 1;) get(#Return) for('IBM US EQUITY')\")","v":{"v":-0.09837630768149397,"d":"-9.84%"}}]

{"sheet":"BQL Examples","index":7,"n_rows":9,"n_cols":3,"rows_kept":9,"merged":[[2,0,2,2]]}
["BQL Examples"]
[]
["BQL Examples"]
["Example","Input","Output"]
["Closing price of IBM on 12/13/2017","BQL(\"IBM US EQUITY\",\"PX_LAST\",\"DATES=2017-12-13\")",{"$f":"_xll.BQL(\"IBM US EQUITY\",\"PX_LAST\",\"DATES=2017-12-13\")","v":153.91}]
["Closing price of IBM on 12/13/2017, rounded to 4 decimals","BQL(\"IBM US EQUITY\",\"round(PX_LAST,4)\",\"DATES=2017-12-13\")",{"$f":"_xll.BQL(\"IBM US EQUITY\",\"round(PX_LAST,4)\",\"DATES=2017-12-13\")","v":153.91}]
["Closing price of IBM on 12/13/2017, rounded to 4 decimals, using a cell-referenced date","BQL(\"IBM US EQUITY\",\"round(PX_LAST,4)\",BQL.Params(\"DATES\",BQL.Params!B5))",{"$f":"_xll.BQL(\"IBM US EQUITY\",\"round(PX_LAST,4)\",_xll.BQL.Params(\"DATES\",BQL.Params!A6))","v":153.91}]
["Average daily closing prices of IBM for 2017","BQL(\"IBM US EQUITY\",\"avg(PX_LAST)\",\"DATES=RANGE(2017-01-01,2017-12-31)\")",{"$f":"_xll.BQL(\"IBM US EQUITY\",\"avg(PX_LAST)\",\"DATES=RANGE(2017-01-01,2017-12-31)\")","v":{"v":157.65800796812752,"d":"157.66"}}]
["Average daily closing prices (rounded to 4 decimals) of IBM for 2017, excluding #N/A's","BQL(\"IBM US EQUITY\",\"avg(round(dropna(PX_LAST),4))\",\"DATES=RANGE(2017-01-01,2017-12-31)\")",{"$f":"_xll.BQL(\"IBM US EQUITY\",\"avg(round(dropna(PX_LAST),4))\",\"DATES=RANGE(2017-01-01,2017-12-31)\")","v":{"v":157.65800796812752,"d":"157.66"}}]