Basis pricing for Physical commodities

@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.

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

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.

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

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

@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

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.