Finding the spread of LQD ETF using Price

Question

I want to know what the calculation is to find LQD Spread using price. The image shown is LQD ETF from Bloomberg, I want to be able to convert 110.24 to 124.82bp in spread. If anybody has a formula or a code, please let me know. Thank you.

LQD Bloomberg ETF

Answer 1

You don't need to do the math.

The G-spread is The YIELD of LQD - the YIELD of the 10 Year government bond... straight forward.

To do this programmatically, just pull the field from Bloomberg

if using excel:

=BQL('LQD US EQUITY','YAS_YLD_SPREAD')

if using VSCode

from xbbg import blp
    
spread = blp.bdp('LQD US EQUITY','YAS_YLD_SPREAD')
spread

Edit:

Looks like he wants to convert using only price since price is real time.... Easy stuff.

looking at the code below, just change 'day_open_5' to 'day' if you want the whole day. You can loop through the day and set a schedule if you want. As you can see, this will return the price intra-day

from xbbg import blp
import pandas as pd

def get_spread(px):
    # Assuming this function returns a single value based on the input px
    return blp.bdp('lqd us equity','yas_yld_spread',yas_bond_px=f'{px}').values[0][0]

# Load your data
data = blp.bdib('lqd us equity','20231226','day_open_5', ref='IndexUS').droplevel(0, axis=1).reset_index()
data.index = data['index'].apply(pd.to_datetime, errors='coerce')
data

To convert to spread, just pass it as an override

# Apply the function to each cell in the specified columns
for col in ['open', 'high', 'low', 'close']:
    data[col] = data[col].apply(get_spread)
data

as you can see, at the price of 110.15 you get a spread of 136.2583 showing the spread is correctly calculated and converted for every tick.

Use this method for your realtime chart. I suggest storing the ticks in a sqlite db. ChatGPT can produce the code in less than 1 minute.

Good luck.