Data Request

gabriel.matejka

Hi,

I'm trying to combine multiple requests for financial and company data, but I'm having trouble with the syntax.

(The EIKON support forwarded me here, I'm honestly not sure whether this is not a little to simple for this forum)

Basically I'm trying to derive the dividend information for the STOXX Europe 600 and the corresponding meta-information (name, iso-code, naicssector-code) and financial information which corresponds to the dividend information.

The Notebook file is uploaded separately (without EIKON app-key)

Any help is appreciated!

All the best,
Gabriel

dividends_data_without_appkey.txt

x = ek.get_data('.STOXX', ['TR.IndexConstituentRIC','TR.IndexConstituentName'])[0]

rics = x.iloc[:,1]
rics = rics.tolist()

# dividend data
df = ek.get_data(rics,
['TR.DivAnnouncementDate',
'TR.DivRecordDate',
'TR.DivUnadjustedGross',
'TR.DivType',
'TR.DivPaymentType',
'TR.DivCurr',
'TR.DivAnnouncementDate',
'TR.DivExDate',
'TR.DivPayDate',
'TR.DivEventStatus',
'TR.DivNotes',
'TR.DividendFrequency',
'TR.DivSourceOfFunds'],
{

"SDate":"2010-01-01","EDate":"2022-09-27",
})[0]
df.to_excel('stoxx_dividends_data.xlsx', sheet_name='sheet1', index=False)
df.head(10)

# meta-information
df = ek.get_data(
#'BAWG.VI',
rics,
['TR.CommonName',
'TR.CompanyIncorpRegion',
'TR.ISIN',
'TR.NAICSSector',
'TR.NAICSIndustryGroup',
'TR.NAICSIndustryGroupCode',
'TR.IssuerRating',
'TR.IssuerRating.Date'],
{

"SDate":"2010-01-01","EDate":"2022-09-27",
})[0]

df.to_excel('stoxx_meta_data.xlsx', sheet_name='sheet1', index=False)
df.head(10)

# financial data
df = ek.get_data(
'BAWG.VI',
#rics,
['TR.Revenue',
'TR.TotalOperatingExpense',
'TR.EBIT',
'TR.EBITDA',
'TR.NetIncomeBeforeTaxes',
'TR.NetIncomeAfterTaxes',
'TR.CashAndSTInvestments',
'TR.TotalAssetsReported',
'TR.TotalLiabilities',
'TR.NetDebt',
'TR.TotalEquity',
'TR.CompanyMarketCap',
'TR.Beta',
'TR.FreeCashFlow'],
{

"SDate":"2010-01-01","EDate":"2022-09-27",
})[0]
df.to_excel('stoxx_financial_data.xlsx', sheet_name='sheet1', index=False)
df.head(10)

# matched datasets (not working at the moment)
# dividend data
df = ek.get_data(rics,
['TR.DivAnnouncementDate',
'TR.DivRecordDate',
'TR.DivUnadjustedGross',
'TR.DivType',
'TR.DivPaymentType',
'TR.DivCurr',
'TR.DivAnnouncementDate',
'TR.DivExDate',
'TR.DivPayDate',
'TR.DivEventStatus',
'TR.DivNotes',
'TR.DividendFrequency',
'TR.DivSourceOfFunds',
'TR.CommonName',
'TR.CompanyIncorpRegion',
'TR.ISIN',
'TR.NAICSSector',
'TR.NAICSIndustryGroup',
'TR.NAICSIndustryGroupCode',
'TR.IssuerRating',
'TR.IssuerRating.Date',
'TR.Revenue',
'TR.TotalOperatingExpense',
'TR.EBIT',
'TR.EBITDA',
'TR.NetIncomeBeforeTaxes',
'TR.NetIncomeAfterTaxes',
'TR.CashAndSTInvestments',
'TR.TotalAssetsReported',
'TR.TotalLiabilities',
'TR.NetDebt',
'TR.TotalEquity',
'TR.CompanyMarketCap',
'TR.Beta',
'TR.FreeCashFlow'],
{

"SDate":"2010-01-01","EDate":"2022-09-27",
})[0]
df.to_excel('stoxx_dividends_data.xlsx', sheet_name='sheet1', index=False)
df.head(10)

jonathan.legrand

Hi @gabriel.matejka ,

I changed some fields and forward filled some cells in the code below; was this closer to what you were looking for?

# dividend data
df1 = ek.get_data(
    instruments = rics,
    fields = ['TR.DivAnnouncementDate',
              'TR.DivRecordDate',
              'TR.DivUnadjustedGross',
              'TR.DivType',
              'TR.DivPaymentType',
              'TR.DivCurr',
              'TR.DivAnnouncementDate',
              'TR.DivExDate',
              'TR.DivPayDate',
              'TR.DivEventStatus',
              'TR.DivNotes',
              'TR.DividendFrequency',
              'TR.DivSourceOfFunds'],
    parameters = {
        "SDate":"2010-01-01","EDate":"2022-09-27"
    })[0]

# df1.to_excel('stoxx_dividends_data.xlsx', sheet_name='sheet1', index=False)
print('df1:')
display(df1)

# meta-information
df2 = ek.get_data(
    rics,  # 'BAWG.VI'
    ['TR.CommonName',
     'TR.IssuerRating.Date',
     'TR.IssuerRating',
     'TR.CompanyIncorpRegion',
     'TR.ISIN',
     'TR.NAICSSector',
     'TR.NAICSIndustryGroup',
     'TR.NAICSIndustryGroupCode'],
    {
        "SDate":"2010-01-01","EDate":"2022-09-27"
    })[0]

# Some data here needs to be forward filled; see here for more info on that: https://stackoverflow.com/questions/27012151/forward-fill-specific-columns-in-pandas-dataframe
# replace field that's entirely space (or empty) with NaN (https://stackoverflow.com/questions/13445241/replacing-blank-values-white-space-with-nan-in-pandas)
import numpy as np
df2 = df2.replace('', np.nan, regex=True)

nanFilledColumns = ['Company Common Name', 'Incorp. Region Code', 'NAICS Sector Name']
df2[nanFilledColumns] = df2[nanFilledColumns].ffill()

# df2.to_excel('stoxx_meta_data.xlsx', sheet_name='sheet1', index=False)
print('df2:')
display(df2)


# financial data
df3 = ek.get_data(
    'BAWG.VI',
    ['TR.F.TotRevenue.date',
     'TR.F.TotRevenue',
     'TR.F.TotRevenue',
     'TR.TR.F.OpExpnTot',
     'TR.EBIT',
     'TR.EBITDA',
     'TR.NetIncomeBeforeTaxes',
     'TR.NetIncomeAfterTaxes',
     'TR.F.CashSTInvstTot',
     'TR.TotalAssetsReported',
     'TR.TotalLiabilities',
     'TR.F.NetDebtInclPrefEqMinIntrtoTotCap',
     'TR.TotalEquity',
     'TR.F.MktCap',
     'TR.Beta',
     'TR.FreeCashFlow'],
    {
        "SDate":"2010-01-01","EDate":"2022-09-27"
    })[0]

# df3.to_excel('stoxx_financial_data.xlsx', sheet_name='sheet1', index=False)
df3.head(10)

wasin.w

Hello @gabriel.matejka

What is the error or issue that you are encountering? I have tested your last request and it gets the following timeout error, is it the same issue?

According to your query, you request a lot of RICs and fields data for a wide range of time. I suggest you try to reduce the number of RICs and fields in the get_data function.

gabriel.matejka

Hi @wasin.w,

yes, that was one of the issues. But I believe I could work around that by partitioning the requests.

However, even when using only a single RIC the problem with the different data-types persists (this is actually more of an issue, because I would like to have all the data for the different dates etc. together without having to check this manually).

Here is a screenshot of how it looks when I do the request:


thank you for your help.

All the best,

Gabriel

df = ek.get_data(

'BAWG.VI',

#rics,

['TR.DivAnnouncementDate',

'TR.DivRecordDate',

'TR.DivUnadjustedGross',

'TR.DivType',

'TR.DivPaymentType',

'TR.DivCurr',

'TR.DivAnnouncementDate',

'TR.DivExDate',

'TR.DivPayDate',

'TR.DivEventStatus',

'TR.DivNotes',

'TR.DividendFrequency',

'TR.DivSourceOfFunds',

'TR.CommonName',

'TR.CompanyIncorpRegion',

'TR.ISIN',

'TR.NAICSSector',

'TR.NAICSIndustryGroup',

'TR.NAICSIndustryGroupCode',

'TR.IssuerRating',

'TR.IssuerRating.Date',

'TR.Revenue',

'TR.TotalOperatingExpense',

'TR.EBIT',

'TR.EBITDA',

'TR.NetIncomeBeforeTaxes',

'TR.NetIncomeAfterTaxes',

'TR.CashAndSTInvestments',

'TR.TotalAssetsReported',

'TR.TotalLiabilities',

'TR.NetDebt',

'TR.TotalEquity',

'TR.CompanyMarketCap',

'TR.Beta',

'TR.FreeCashFlow'],

{

"SDate":"2010-01-01","EDate":"2022-09-27",

})[0]

df.to_excel('data_bawag.xlsx', sheet_name='sheet1', index=False)

df.head(10)

jonathan.legrand

Hi @gabriel.matejka,

It does not look like the field 'revenue' exist for the RICs you're after, e.g.:

To find fields that exist for any one instrument, please use the Data Item Browser as per this video.

gabriel.matejka

Yes, thank you for the support!!
All the best,

Gabriel