Basis pricing for Physical commodities

Asher

I want to get FX broken dates calculator to hedge basis price contracts for commodities e.g. Cotton. Sample code as below:
df = ld.get_history('JCI-CTN-ANHUI','TRDPRC_1',start='01-Jan-2025',end='26-Jun-2025') #This is spot price for China Anhui cotton.
df2 = ld.get_history('CCFc1','TRDPRC_1',start='01-Jan-2025',end='26-Jun-2025') #This is the future price for China cotton.
df3 = ld.get_history('CNY=','BID',start='01-Jan-2025',end='26-Jun-2025') #This is the currency exchange RIC for USD/CNY.
df4 = (df['TRDPRC_1']/df3['BID'])-(df2['TRDPRC_1']/df3['BID']) #This is to calculate the basis price then converted to USD.
print(df4)

However, I want to change df3 to FX Forward outright for a broken date in this case the expiry date of the contract e.g. CCFc1.

API Playground

I managed to create the code below, however I am not sure how I can incorporate the end date as the expiry date. The code I use to get expiry date is ld.get_data('CCFc1','EXPIR_DATE'). Also I am not sure how I can get the raw data values from the below code to be used in the initial code replacing df3 or the CNY= spot rate value.

request_definition = ld.delivery.endpoint_request.Definition(
url="https://api.refinitiv.com/data/quantitative-analytics/v1/financial-contracts",
method=ld.delivery.endpoint_request.RequestMethod.POST,
body_parameters={
"fields": [
"InstrumentTag",
"ValuationDate",
"InstrumentDescription",
"FxOutrightCcy1Ccy2"
],
"universe": [
{
"instrumentDefinition": {
"instrumentTag": "00102700008910C",
"fxCrossType": "FxForward",
"fxCrossCode": "USDCNY",
"legs": [
{
"endDate": "2025-07-14T00:00:00Z"
}
]
},
"pricingParameters": {
"valuationDate": "2025-06-27T00:00:00Z",
"priceSide": "Mid"
},
"instrumentType": "FxCross"
}
],
"outputs": [
"Data",
]
}
)
response = request_definition.get_data()
response.data.raw"

Jirapongse

@Asher

Thank you for reaching out to us.

Regarding the financial-contracts, you can refer to the IPA Financial Contracts: FX Cross Contracts document and the examples on GitHub.

This forum is dedicated to software developers using LSEG APIs. The moderators on this forum do not have deep expertise in every bit of content available through LSEG products, which is required to answer content questions such as this one.

The best resource for content questions is the Helpdesk support team, which can be reached by submitting queries through LSEG Support. The support team will either have the required content expertise ready available or can reach out to relevant content experts to get the answer for you.

You need to ask for the Excel formula, such as =RDP.Data, that can be used to retrieve the required data.

Then, we can help you converting that formula to Python code.

Asher

The help desk told me to post that question here to get it answered so I don't know what you want me to do?

Jirapongse

This forum is for API technical questions. Both code can return data properly.

You can refer to the IPA Financial Contracts: FX Cross Contracts document regarding how to use this API or contact the Instrument Pricing Analytics - Delivery Platform team directly via LSEG Support to verify the request message.

Asher

@Jirapongse my question is how can I use the forward calculation to calculate the basis for buying physical today against the commodity futures expiration date?

ie buy Chinese cotton today, deliver it according to the expiration date of the futures contract, you need to hedge the fx risk, and so you need to use the fx fwd calc and then you can calculate your basis? How do I do that?

Jirapongse

@Asher

I have submitted this question on your behalf to the Instrument Pricing Analytics - Delivery Platform team.

The case number is 14945360. The support team can verify this request message.

Asher

Thanks

Jerick.DavidRWIB

pasting code snippet we have come up for current use case

from datetime import datetime

#future price
ed = ld.get_data('CCFc1','BID')
#physical price - future price
#cottonPC = ld.get_data('JCI-CTN-ANHUI','TRDPRC_1')
cottonPC = 15350
#todays basis
basis = cottonPC - int(ed.iat[0, 1])
print(basis)

#number of days until expiration
expirationdate = ld.get_data('CCFc1','EXPIR_DATE')
#get date data and convert to string
#we actually want to use first delivery date
expd = expirationdate.iat[0, 1]
#original_string = str(expirationdate.iat[0, 1])
#replace space with T and add Z on the end 
#to match date format input for IPA api call
#endDate1 = original_string.replace(" ", "T") + "Z"
#print(endDate1)

#format date data to dd-mmm-yyyy
#endDate2 = ed.iat[0, 1].strftime("%d-%b-%Y")
#print(endDate2)
today = date.today()
difference = expd.date() - today
dnum = difference.days
print(dnum)
#replace space with T and add Z on the end 
#to match date format input for IPA api call
expd_ipa = str(expd).replace(" ", "T") + "Z"
valuation_ipa = str(today)+"T00:00:00Z" 

request_definition = ld.delivery.endpoint_request.Definition(
url="https://api.refinitiv.com/data/quantitative-analytics/v1/financial-contracts",
method=ld.delivery.endpoint_request.RequestMethod.POST,
body_parameters={
"fields": [
"InstrumentTag",
"ValuationDate",
"InstrumentDescription",
"FxOutrightCcy1Ccy2"
],
"universe": [
{
"instrumentDefinition": {
"instrumentTag": "00102700008910C",
"fxCrossType": "FxForward",
"fxCrossCode": "USDCNY",
"legs": [
{
"endDate": expd_ipa #expiration date
}
]
},
"pricingParameters": {
"valuationDate": valuation_ipa, #todays date
"priceSide": "Mid"
},
"instrumentType": "FxCross"
}
],
"outputs": [
"Data",
]
}
)
response = request_definition.get_data()
fxdata = response.data.raw
print(fxdata['data'][0][0])
print(fxdata['data'][0][1])
print(fxdata['data'][0][2])
print(fxdata['data'][0][3]) #divide basis with this value

#basis in value per ton of cotton
basisUSDTon = basis/int(fxdata['data'][0][3])

#need to convert to pound of cotton
basisUSDlbp = int(basisUSDTon)/2204.62
print(basisUSDlbp)

We would like to ask your assistance in updating code to automatically compute daily.

Asher

@Jirapongse how can we make this code more modular rather than brute forcing it

Jirapongse

@Asher

You can use the Python functions to make code more modular.

Asher

can you do it for me please

Jirapongse

@Asher

Typically, this forum doesn't provide the coding service. We can share the simple snippet code that demonstrates how to use our APIs.

For coding services, you may contact your LSEG account team or sales team directly to request professional services. The Professional Services group can review your source code or assist with implementing the solution for an additional fee.

Asher

so what is this forum for then, I want you to demostrate how to use the api to price commodity basis thats all I am asking you to do, it isn't that hard is it?

Jirapongse

@Asher

I have reviewed the question. You would like to change df3 which is historical spot of CNY= to the data from financial-contracts.

df3 = ld.get_history('CNY=','BID',start='01-Jan-2025',end='26-Jun-2025') #This is the currency exchange RIC for USD/CNY.

The df3 code requests the historical data from 01-Jan-2025 to 26-Jun-2025.

According to the helpdesk team, the financial-contracts request looks like this.

request_definition = ld.delivery.endpoint_request.Definition(

url="https://api.refinitiv.com/data/quantitative-analytics/v1/financial-contracts",

method=ld.delivery.endpoint_request.RequestMethod.POST,

body_parameters={

"fields": [

"InstrumentTag",

"ValuationDate",

"InstrumentDescription",

"FxOutrightCcy1Ccy2"

],

"universe": [

{

"instrumentDefinition": {

"instrumentTag": "00102700008910C",

"fxCrossType": "FxForward",

"fxCrossCode": "USDCNY",

"legs": [

{
"endDate": expd_ipa #expiration date

}

]

},

"pricingParameters": {
"valuationDate": valuation_ipa, #todays date

"priceSide": "Mid"

},

"instrumentType": "FxCross"

}

],

"outputs": [

"Data",

]

}

)

response = request_definition.get_data()

fxdata = response.data.raw

The value of expd_ipa is 2025-07-14 and the value of valuation_ipa is 2025-07-09 (today).

The output of this request is:

00102700008910C

2025-07-09T00:00:00Z

FxForward USDCNY 2025-07-14

7.1818605

I assume that the value (7.1818605) is for 2025-07-09 (valuationDate).

I am not sure if you would like to use this value (7.1818605), as a constant with df and df2 to calculate the df4. For example:

df4 = (df['TRDPRC_1']/7.1818605)-(df2['TRDPRC_1']/7.1818605)

Or, you would like to get the output of the financial-contracts request for other valuationDate values from 01-Jan-2025 to 26-Jun-2025 to calculate the df4. The expiration date (2025-07-14) in the "endDate" is the same for all financial-contracts requests. For example, the following code will request the data for two valuationDate dates (2025-01-01 and 2025-01-02) with the same endDate.

valuation_ipa = '2025-06-25'
request_definition = ld.delivery.endpoint_request.Definition(
url="https://api.refinitiv.com/data/quantitative-analytics/v1/financial-contracts",
method=ld.delivery.endpoint_request.RequestMethod.POST,
body_parameters={
  "fields": [
    "InstrumentTag",
    "ValuationDate",
    "InstrumentDescription",
    "FxOutrightCcy1Ccy2"
  ],
  "universe": [
    {
      "instrumentDefinition": {
        "instrumentTag": "00102700008910C",
        "fxCrossType": "FxForward",
        "fxCrossCode": "USDCNY",
        "legs": [
          {
            "endDate": "2025-07-14"
          }
        ]
      },
      "pricingParameters": {
        "valuationDate": "2025-01-01",
        "priceSide": "Mid"
      },
      "instrumentType": "FxCross"
    },
    {
      "instrumentDefinition": {
        "instrumentTag": "00102700008910D",
        "fxCrossType": "FxForward",
        "fxCrossCode": "USDCNY",
        "legs": [
          {
            "endDate": "2025-07-14"
          }
        ]
      },
      "pricingParameters": {
        "valuationDate": "2025-01-02",
        "priceSide": "Mid"
      },
      "instrumentType": "FxCross"
    }
  ],
  "outputs": [
    "Data"
  ]
}
)
response = request_definition.get_data()
fxdata = response.data.raw
fxdata

The output is:

{'data': [['00102700008910C',

   '2025-01-01T00:00:00Z',

   'FxForward USDCNY 2025-07-14',

   7.1802015625],

  ['00102700008910D',

   '2025-01-02T00:00:00Z',

   'FxForward USDCNY 2025-07-14',

   7.184685]]}

Then, use 7.1802015625 to calculate a basis price on 2025-01-01 and use 7.184685 to calculate a basis price on 2025-01-02.