bbgql
1. 64110LAE6 Corp YTM for 10 days- when there is no price please leave 0. 2. Also I want 1 year oas spread on monthly basis
'=BQL("64110LAE6 Corp","ZNAV(YIELD(yield_type=YTM,dates=range(-10d,0d),pricing_source=BGN))") '=BQL("64110LAE6 Corp","SPREAD(spread_type=OAS,dates=range(-1y,0d))","frq=m")
ASW spread of Netflix bond(64110LAE6 Corp) over 10 days but sort by the spread. I don't like to see NA errors but keep the dates please.
'=BQL("64110LAE6 Corp","sort(SPREAD(spread_type=ASW,dates=range(-10d,0d)))","excelfill=b")
Can I get the worst 3 day price change of AAPL in 3 years?
'=BQL("AAPL US Equity","MIN(ROLLING(PCT_CHG(dropna(PX_LAST(Dates=Range(-3d,0d)))), iterationdates=range(-3y,0d)))")
How do I pull AAPL and IBM's Last Price for the last 7 days without NA?
'=BQL("AAPL US Equity,IBM US Equity","dropna(PX_LAST(dates=range(-7d,0d)))")
BMW GR Equity dividend and corporation adjusted price for 8 days but I want to see this sorted by its price(descending).
'=BQL("BMW GR Equity","sort(PX_LAST(CA_ADJ=FULL,Dates=Range(-8d,0d)))")
Please get 30th percentile value of CL1 COMDTY over the last 2 years
'=BQL("CL1 Comdty","Quantile(PX_LAST(dates=range(-2y,0d)),0.3)")
How do I retrieve the most recent OAS spread of the VW 3.4 Perp bond using BQL()?
'=BQL("EK811579 Corp","SPREAD(spread_type='OAS',fill=prev)") 'get(SPREAD(spread_type='OAS',fill=prev)) for('EK811579 Corp')
Download 3 month daily price change time series. Please avoid any NA.
'=BQL("IBM US Equity","PCT_DIFF(Dropna(px_last(dates=range(-3m,0d))))")
How do I pull IBM's three month monthly price time series? Please output horisontally.
'=BQL("IBM US Equity","PX_LAST(Dates=Range(-3m,0d),FRQ=M)","transpose=t")
How do I pull IBM's price for last 3 days? Please output horisontally
'=BQL("IBM US Equity","PX_LAST(dates=range(-3d,0d))","transpose=t")
Please get NFLX US Equity 1 year average price
'=BQL("NFLX US Equity","AVG(PX_LAST(dates=range(-1y,0d)))"))
I would like to see the price history of NFLX for 2 years but please display only the days they have recorded price greater than 400 USD
'=BQL("NFLX US Equity","Matches(PX_LAST(Dates=Range(-2y,0d)), PX_LAST(Dates=Range(-2y,0d))>400)")
How do I pull IBM's Last Price for the last 7 days without NA?
'=BQL("ibm us equity","dropna(px_last(dates=range(-7d,0d)))") '=BQL("ibm us equity","px_last(dates=range(-7d,0d))","excelfill=b")
LAST/BID/ASK price for IBM US Equity, VOD LN Equity and SIE GR Equity with one syntax.
'=BQL("ibm us equity","px_last,px_bid,px_ask")
Please pull the data from the previous question, but for the last 5 days
'=BQL("ibm us equity","px_last,px_bid,px_ask","dates=range(-5d,0d)")
Please retrieve NFLX US Equity's 3 month price change as of 2019-01-20
'=BQL("nflx us equity","net_chg(dropna(px_last(dates=range(2019-01-20-3m,2019-01-20))))")
From Practice E Please get only the highest and lowest % change
'LET(#CHG_MEMBER = PCT_CHG(PX_LAST(DATES=RANGE(-1M,0D))); #CHG_AVG = GROUPAVG(#CHG_MEMBER); #SPREAD = #CHG_MEMBER - #CHG_AVG;) GET(MIN(GROUP(#SPREAD)), MAX(GROUP(#SPREAD))) FOR(MEMBERS('INDU INDEX')) WITH(MODE=CACHED)
For GBP Curncy, I want to get todays price but if its lower than the 1month average price, I want to have the Average price
'LET(#PX_TODAY=PX_LAST().value; #AVG=AVG(PX_LAST(DATES=RANGE(-1M,0D)).value); #PX=IF(#PX_TODAY<#AVG, #AVG, #PX_TODAY);) GET(#PX) FOR('GBP CURNCY') WITH(MODE=CACHED)
Naming Columns: You can name your columns with As #name. If you use the example for Z-Spread How do I retrieve the mopst recent OAS spread of the VW 3.4 Perp bond using BQL.Query()?
'get(SPREAD(spread_type='OAS',fill=prev) as #OAS) for('EK811579 Corp')
Let(): We can use the let clause to label expressions that can be reused later. This makes more powerful queries easier to create and manage. How do I pull IBM bond's Average Z-spread for the Last 5 Weeks excluding non-trading days using BQL.Query() and let?
'let( #spreads = dropna(SPREAD(Spread_Type='Z',dates=range(-5w,0d))); #avg_spread = avg(#spreads); ) get(#avg_spread) for('AM2694405 Corp') Equivalent of let() in BQL() = '=BQL("AM2694405 Corp","#avg_spread","#spreads = dropna(SPREAD(Spread_Type='Z',dates=range(-5w,0d)))","#avg_spread = avg(#spreads)")
Preferences
(Optional) Additional Global Parameters that relate to data and error handling (to be discussed)
With()
(Optional) Global Parameters that are applied to all data items and functions they are applicable to
Let()
(Optional) label expressions for later reuse in your query
Pull the standard deviation of AAPL's FY2 Sales estimate amongst analysts
=BQL("AAPL US EQUITY","CONTRIBUTOR_STATS(SALES_REV_TURN(FPT=A,FPO=2,AE=E), STD)")
Apple's EBITDA last 20 quarters, ranked but would like to see only the top 5:
=BQL("AAPL US EQUITY","LAST(sort(RANK(EBITDA(FPO=RANGE(-19,0),FPT=Q))), 5)","cols=2;rows=7")
Apple's EBITDA last 20 quarters, ranked:
=BQL("AAPL US EQUITY","RANK(EBITDA(FPT=Q,FPO=RANGE(-19,0)))","ShowHeaders=F","ShowIDs=F","ShowDates=F","cols=1;rows=20")
Find the lowest next year EPS estimate for ATM NZ Equity out of all contributing analysts
=BQL("ATM NZ Equity","Contributor_Stats(IS_EPS(FPO=2,FPT=A,FPR=RANGE(2014,2019)),MIN)","cols=2;rows=8")
Get Costco's next year's calendar sales value as of today and as of 2 years ago.
=BQL("COST US EQUITY","SALES_REV_TURN(AE=E, FPT=A,FPO=1, DATES=2020-04-23) as #TODAY, SALES_REV_TURN(FPT=A,FPO=1, DATES=2020-04-23) as #TWOYEARSAGO","cols=3;rows=3")
Find the top 10 securities in Australia's communications sector, based on market cap.
=BQL("FILTER(EQUITIESUNIV(['ACTIVE','PRIMARY']), GICS_SECTOR_NAME=='COMMUNICATIONS' AND GROUPRANK(CUR_MKT_CAP)<11)","NAME")
From the BCOM Index Please screen the securities for sugar and coffee
=BQL("FILTER(MEMBERS('BCOM INDEX'),RIGHT(NAME,5)=='SUGAR' OR LEFT(NAME,6)=='COFFEE')","NAME","ShowIDs=F")
For SPX Index, Please screen the securities that have average 1 month trading volume greater than 10 Million.
=BQL("FILTER(MEMBERS(['SPX INDEX']), AVG(TURNOVER(DATEs=RANGE(-1M,0D)))>1000M)","NAME","cols=2;rows=9")
For LP01TREU Index, Please find the securities within the Financials sector.
=BQL("FILTER(MEMBERS(['SPX INDEX']),GICS_SECTOR_NAME=='Financials')","NAME","cols=2;rows=67")
Find all Australian stocks with constant earnings growth over the last 5 quarters (every quarter has grown from the last)
=BQL("Filter(EquitiesUniv(['Active', 'Primary']), EXCH_CODE=='AU' AND MIN(PCT_DIFF(IS_EPS(FPT=Q, FPO=RANGE(-4, 0))))>0)","Name","mode=cached")
For INDU Index, screen for securities that have pe_ratio greater than 12 and are in the Information technology sector
=BQL("Filter(Members(['INDU Index']), PE_RATIO>12 AND GICS_SECTOR_NAME=='Information Technology')","NAME","cols=2;rows=6")
Find all US Equities that have a market cap greater than 10 million and have grown in earnings over the last 5 years (PoP using annual data)
=BQL("Filter(equitiesuniv(['Active','Primary']), EXCH_CODE=='US' AND CUR_MKT_CAP(Currency=USD)>100M AND Security_typ=='Common Stock' AND PCT_CHG(IS_EPS(FPT=A,FPO=RANGE(-4,0)))>0)","NAME","showids=F")
For IBM US Equity, get the most recent 5 year average sales figure, in millions:
=BQL("IBM US EQUITY","AVG(SALES_REV_TURN(FPT=A,FPO=RANGE(-4,0)))")
=BQL("Members('BGEI Index')","COUNT(GROUP(ID, COUNTRY_FULL_NAME))","cols=2;rows=43")
=BQL("MEMBERS('BGEI INDEX')","WAVG(GROUP(PE_RATIO,GICS_SECTOR_NAME),GROUP(ID().WEIGHTS,GICS_SECTOR_NAME))","currency=usd","cols=2;rows=12")
Using the INDU Index. I would like to retrieve the spread between 1 month % change of each member and average % Change of the overall index.
=BQL("MEMBERS('INDU INDEX')","PCT_CHG(PX_LAST(DATES=RANGE(-1M,0D)))-GROUPAVG(PCT_CHG(PX_LAST(DATES=RANGE(-1M,0D))))","cols=2;rows=31")
Find the 1 year ASW Spread average for LP01TREU Index
=BQL("MEMBERS('LP01TREU Index')","AVG(GROUP(SPREAD(SPREAD_TYPE=ASW,DATES=RANGE(-1Y,0D))))")
Please retrieve the number of the securities for BGEI Index. I also would like to see this for each country.
=BQL("Members('BGEI Index')","COUNT(GROUP(ID, COUNTRY_FULL_NAME))","cols=2;rows=43")
Pull in all of the special cash dividends issued by Cracker Barrel over the last 2 years, according to declaration date
=BQL("Members('SPX Index')", "Count(Group(CASH_DIVS(Dates=Range(-5Y,0D))))","showquery=t")
Replicate this: =BDP("NFLX US Equity","BEST_EBITDA", "BEST_FPERIOD_OVERRIDE=1BF")
=BQL("NFLX US EQUITY","EBITDA(FPO=1,AE=E,FPT=BT)")
Get PE_ratio of Netflix for fiscal year 2010:
=BQL("NFLX US EQUITY","PX_LAST(DATES=2010-12-31)/IS_EPS(FPR=2010,FPT=A,AE=A)
Please retrieve NFLX US Equity's 3 month price change
=BQL("NFLX US Equity","NET_CHG(dropna(PX_LAST(dates=range(-3m,0d))))")
For SIE GR Equity, Get CAGR for 10 year actual sales.
=BQL("SIE GR Equity","COMPOUNDGROWTHRATE(SALES_REV_TURN(AE=A,FPT=A,FPO=RANGE(-9,0)))")
I would like to get the Sector median PE ratio of a single universe combining the SPX index and the UKX index
=BQL("UNION(members('SPX Index'),members('UKX Index'))","MEDIAN(GROUP(PE_RATIO,GICS_SECTOR_NAME))","cols=2;rows=13")
Find the 5 members in the AS51 index that have the highest average traded value over the last 30 days in AUD but show only the volume in shares and sort the outpur from greatest to lowest volume.
=BQL("filter(members(['AS51 Index']),Grouprank(AVG(TURNOVER(Currency=AUD,Dates=Range(-30D,0D))))<6)","GROUPSORT(PX_VOLUME)","Name","cols=2;rows=6")
How do I pull IBM's Last Price for the last 7 days?
=BQL("ibm us equity","PX_last(dates=range(-7d,0d))")
Get()
DataItem(Parameters) to be retrieved
I am looking at CL1 Comdty daily % change for 1 month. Retrieve 'up' when price change was positive get 'down' when negative.
LET(#PX=PCT_DIFF(PX_LAST(Dates=Range(-1M,0D)));)
Find the top and bottom 10 performers in the US based on 1W percent change in price. This should return 20 securities - 10 top performers and 10 bottom performers. Sort them in the output by their performance.
LET(#PX_CHG = PCT_CHG(PX_LAST(DATES=RANGE(-1W,0D))); #RANK_UP = GROUPRANK(SORT(#PX_CHG, ORDER=ASC)); #RANK_DOWN = GROUPRANK(SORT(#PX_CHG, ORDER=DESC));) GET(#PX_CHG) FOR(FILTER(EQUITIESUNIV(['ACTIVE', 'PRIMARY']), CUR_MKT_CAP(CURRENCY=USD)>10M AND SECURITY_TYP=='Common Stock' AND EXCH_CODE=='US' AND #RANK_UP<11 OR #RANK_DOWN<11)) WITH(MODE=CACHED)
For()
Security, wrapped with single quotes '
Using the query syntax, pull in the dispersion in the prices of Disney and Netflix. We are doing to define dispersion as the highest price of the day minus the lowest price of the day, divided by the mid price of the day.
let(#DISPERSION= (PX_HIGH-PX_LOW)/PX_MID;) get(#DISPERSION) for(['DIS US EQUITY','NFLX US EQUITY'])
Using the query syntax, pull in the price of IBM as #px and earning per share as #eps. Then create a variable #pe_ratio that is the ratio of #px and #is_eps.
let(#px = PX_LAST; #eps = IS_EPS; #pe_ratio = #px/#eps;) get(#pe_ratio) for('IBM US Equity')
For NFLX US Equity, please get 1 year 5th percentile price value and 10th percentile value. Display the dates/prices of when we have recorded price between these. This is a more complex query - make sure that you ask the teacher if you dont understand the logic
let(#px = dropna(PX_LAST(dates=range(-1y,0d))); #5 = quantile(#px,0.05); #10 = quantile(#px,0.10); #matches = matches(#px,#px > #5 and #Px < #10);) get(#matches) for('NFLX US Equity')
