Need help to update the Python script that can fetch a price history starting from 2008 (or the

karenallen.eusebio

Query: I am trying to draft a Python script that can fetch a long price history starting from 2008 (or the launch date of the RIC, whichever is later) and insert the data into our database.

However, after multiple attempts I am still facing one issue after another, and I am unable to get the script working end-to-end.

I was wondering if you could help me draft the correct version of this script or guide me with the best approach to achieve this

import pandas as pd
import win32com.client
import subprocess
import json
import requests
import re
import eikon as ek
import lseg.data as ld
from urllib.request import urlopen
from operator import itemgetter
import openpyxl
from datetime import date,timedelta,datetime

from openpyxl.drawing.text import RichTextProperties

today = date.today()#-timedelta(1)
date_time = today
date_time = date_time.strftime("%Y-%m-%d")

print('Connecting to Eikon')
#ld.set_log_level(0)
ld.open_session(app_key='REMOVED')
print('Connection Established to Eikon')

RICs = pd.read_excel(r'REMOVED\Eikon API Snapper\PKEY_PYTHON_LIBRARY.xlsx', sheet_name='List', header=None)
RICs = RICs[0].astype(str).tolist()

for RIC1 in RICs:
RIC = "'" + RIC1 + "'"

print("Checking for:", RIC1)


while True:

    try:

        df = ld.get_history([RIC1], start = '2007-12-31',end = '2015-12-31',interval='daily',fields = ['TRDPRC_1'])

        #df = ld.get_history([RIC1], start = '2022-08-01',end = '2025-09-17',interval='daily',fields = ['TRDPRC_1'])

        break

    except ek.EikonError as err:

        if err.code != 2504:

            # request failed with other reason than 2504 error code then stop ... if 2504 then retry

            break

        #retry

        print("retrying API snap...")

        timer.wait(1)



#df = ek.get_timeseries([RIC1], start_date = '2006-12-01',end_date = '2018-12-31',interval='daily',fields = ['CLOSE'])

#df = ek.get_timeseries([RIC1], start_date='2018-12-01', end_date='2024-06-30', interval='daily', fields=['CLOSE'])


#this is for pkey to ric dictionary

Lib = pd.read_excel(r'S:\\Eikon API Snapper\PKEY_PYTHON_LIBRARY.xlsx', sheet_name='Library', header=None)

Pkey = dict(zip(Lib[0], Lib[1]))


df = df.iloc[2:]


if df.shape[1] == 1:

    df.columns = ['Close']

    df['Date'] = df.index

else:

    df.columns = ['Date', 'Close']


df.reset_index(drop=True, inplace=True)


df['Date'] = pd.to_datetime(df.Date)

df['Date'] = df['Date'].dt.strftime("'%d%b%Y'")

df['Instrument'] = pd.Series([RIC1 for _ in range(len(df.index))])

df['Pkey'] = df['Instrument'].map(Pkey)

print(df.tail(3))


df['sql_query'] = df.apply(lambda r: "call CORE_VALUATIONS.VALUATIONS.INSERTEQCLOSINGPRICE(%s, %s, %s, NULL, NULL)" % (r['Pkey'], r['Date'], r['Close']), axis=1)

payload = df['sql_query']

p = '\n'.join(str(e) for e in payload)

print("Sample of Stored Procedures generated for", RIC1)

print('\n'.join(payload.tail(3).astype(str)))


headers = {'Content-Type': 'text/plain'}

url = "https://pv-marketdata-store.ihsmvals.com/sql/"

responseupdate = requests.request("POST", url, headers=headers, data=p)

print(responseupdate)

print("Data successfully inserted for RIC:", RIC1)

Gurpreet

Please DO NOT post client information or credentials in these public forums !!