API Queries: misaligned data

If I try the below query, it looks good:

response= fundamental_and_reference.Definition(

["5000274109"],

["AVAIL(TR.F.PERIODENDDATE(STTYPE=BAL), TR.F.PERIODENDDATE(STTYPE=INC), TR.F.PERIODENDDATE(STTYPE=CAS))","AVAIL(TR.F.PERIODENDDATE(STTYPE=BAL).fperiod, TR.F.PERIODENDDATE(STTYPE=INC).fperiod, TR.F.PERIODENDDATE(STTYPE=CAS).fperiod)", "TR.F.EBITDAMargPct"],

{"SDate": -40, "EDate": -55, "FRQ": "FI", "Period": "FI0"}

)

If I add the extended_params I need for fixing other misalignments, this result is misaligned:

response= fundamental_and_reference.Definition(

["5000274109"],

{"SDate": -40, "EDate": -55, "FRQ": "FI", "Period": "FI0"},

extended_params = {"output":"Col,T|Va,Row,In,PERIODENDDATE|"}

)

—

Also I tried my initial query not for -40 to -50 periods but with the last 15 years and I can reproduce the error even in the codebook:

pd.set_option("display.max_rows", None)
today = datetime.now()
EDATE = today.strftime("%Y-%m-%d") # Today's date in YYYY-MM-DD format
SDATE = (today.replace(year=today.year - 15, month=1, day=1)).strftime("%Y-%m-%d") # 15 years earlier, January 1st
response= fundamental_and_reference.Definition(
    ["5000274109"],
    ["AVAIL(TR.F.PERIODENDDATE(STTYPE=BAL), TR.F.PERIODENDDATE(STTYPE=INC), TR.F.PERIODENDDATE(STTYPE=CAS))","AVAIL(TR.F.PERIODENDDATE(STTYPE=BAL).fperiod, TR.F.PERIODENDDATE(STTYPE=INC).fperiod, TR.F.PERIODENDDATE(STTYPE=CAS).fperiod)", "TR.F.EBITDAMargPct"],
    {"SDate": EDATE, "EDate": SDATE, "FRQ": "FI", "Period": "FI0"},
    extended_params = {"output":"Col,T|Va,Row,In,PERIODENDDATE|"}
).get_data()
response.data.df

Please confirm why I'm getting duplicate date ( 2013-09-30) when using the below SDATE and EDATE

EDATE = today.strftime("%Y-%m-%d") # Today's date in YYYY-MM-DD formatSDATE = (today.replace(year=today.year - 15, month=1, day=1)).strftime("%Y-%m-%d") # 15 years earlier, January 1st

Find more posts tagged with

python

codebook

#technology

Accepted answers

All comments

Jirapongse

@Norland

Thank you for reaching out to us.

Codebook uses the layout parameter. The code should look like this:

pd.set_option("display.max_rows", None)
today = datetime.now()
EDATE = today.strftime("%Y-%m-%d")  # Today's date in YYYY-MM-DD format
SDATE = (today.replace(year=today.year - 15, month=1, day=1)).strftime("%Y-%m-%d")  # 15 years earlier, January 1st
response= fundamental_and_reference.Definition(
    ["5000274109"], 
    ["AVAIL(TR.F.PERIODENDDATE(STTYPE=BAL), TR.F.PERIODENDDATE(STTYPE=INC), TR.F.PERIODENDDATE(STTYPE=CAS))","AVAIL(TR.F.PERIODENDDATE(STTYPE=BAL).fperiod, TR.F.PERIODENDDATE(STTYPE=INC).fperiod, TR.F.PERIODENDDATE(STTYPE=CAS).fperiod)", "TR.F.EBITDAMargPct"], 
    {"SDate": EDATE, "EDate": SDATE, "FRQ": "FI", "Period": "FI0"},
    extended_params = {"layout":
                       {
                           "columns":[{"item":"dataitem"}], 
                           "rows":[{"item":"PERIODENDDATE"},{"item":"Instrument"}]}
                      }).get_data()
response.data.df

The output is:

Norland

Thank you for your quick response.

Is there a way to remove the NA result?

Also, can you please check as well the first query? Should the client still use extended_params = {"output":"Col,T|Va,Row,In,PERIODENDDATE|"}

Jirapongse

@Norland

The client can use the dropna function of the dataframe to remove NA rows.

response.data.df.dropna()

The output parameter is for the RDP datagrid. The layout parameter is for the UDF datagrid.

By default, the desktop and codebook session uses the UDF datagrid so the layout must be used.

EXPLORE OUR SITES