Hi QC forum

I am experiencing some problems trying to merge some fundamental data dataframe, with an dataframe of returns of the same stocks. I want a nice and clean dataframe with both fundamental data, and return of different stocks. The problem is only in the backtest environment, and not in research. The code for the research is as following

qb = QuantBook()


tickers = ("AAPL", "MSFT", "AMZN", "TSLA", "GOOGL", "FB", "NVDA", "BRK.B", "UNH", "JNJ", "PG", "HD", "V", 
           "BAC", "MA", "XOM", "PFE", "DIS", "CVX", "PEP", "CSCO", "ADBE", "KO", "ABBV", "TMO", "CMCSA", "ABT", "VZ", "AVGO")
           
symbols = [qb.AddEquity(ticker, Resolution.Daily).Symbol for ticker in tickers]

start_time = datetime(2017, 1, 2)
end_time = datetime(2020, 1, 2)

funda_start = datetime(2020, 1, 1)
funda_end = datetime(2020, 1, 2)

fundamentals = ["ValuationRatios.SustainableGrowthRate", "ValuationRatios.PayoutRatio", "ValuationRatios.CashReturn", "ValuationRatios.FCFRatio", "ValuationRatios.EarningYield", "ValuationRatios.SalesYield", "ValuationRatios.BookValueYield", "ValuationRatios.PBRatio",
               "ValuationRatios.PEGRatio", "ValuationRatios.BuyBackYield", "ValuationRatios.PricetoEBITDA", "ValuationRatios.ForwardPERatio", "ValuationRatios.EVToEBITDA3YrAvgChange",
               "ValuationRatios.EVtoFCF", "ValuationRatios.EVtoEBIT", "ValuationRatios.EVToForwardEBITDA", "ValuationRatios.EVToForwardRevenue", "ValuationRatios.ForwardROE",
               "ValuationRatios.ForwardDividend"]
               
ratios = pd.DataFrame()
for fundamental in fundamentals:
    ratio = qb.GetFundamental(symbols, fundamental, funda_start, funda_end)
    ratio.columns = pd.MultiIndex.from_tuples([(fundamental, x) for x in ratio.columns])
    ratios = pd.concat([ratios, ratio], axis = 1)
    
dataframe = ratios.stack().reset_index()
dataframe.drop(["level_0"], axis=1, inplace = True)
dataframe = dataframe.set_index(keys = dataframe.level_1).drop(['level_1'], axis=1)
dataframe.index.name = None

returns = qb.History(symbols, start_time, end_time, Resolution.Daily)
returns = returns.drop_duplicates().close.unstack(level=0)
returns = returns.iloc[0] / returns.iloc[-1]

#concat can also be used perfectly. dataframe = pd.concat([dataframe, returns), axis=1)
dataframe['returns'] = returns       

Where dataframe is a nice and clean dataframe with no issues. 

The code for the backtest is as follows, but not producing a nice dataframe like research.

securities = [x for x in symbols]
    
    #our fundamental values
    fundamentals = ["ValuationRatios.SustainableGrowthRate", "ValuationRatios.PayoutRatio", "ValuationRatios.CashReturn", "ValuationRatios.FCFRatio", "ValuationRatios.EarningYield", "ValuationRatios.SalesYield", "ValuationRatios.BookValueYield", "ValuationRatios.PBRatio",
               "ValuationRatios.PEGRatio", "ValuationRatios.BuyBackYield", "ValuationRatios.PricetoEBITDA", "ValuationRatios.ForwardPERatio", "ValuationRatios.EVToEBITDA3YrAvgChange",
               "ValuationRatios.EVtoFCF", "ValuationRatios.EVtoEBIT", "ValuationRatios.EVToForwardEBITDA", "ValuationRatios.EVToForwardRevenue", "ValuationRatios.ForwardROE",
               "ValuationRatios.ForwardDividend"]

    #setting empty dataframe that we can concat with
    dataframe = pd.DataFrame()

    #Get the returns of the stocks, and get them shaped proberly
    dataframe = algorithm.History([x.Symbol for x in securities], returnLookback, Resolution.Daily)
    dataframe = dataframe.drop_duplicates().close.unstack(level=0)
    dataframe = dataframe.iloc[0] / dataframe.iloc[-1]
    dataframe = pd.DataFrame(dataframe)
    dataframe = dataframe.rename(columns={0: 'returns'})

    fundamental_dataframe = pd.DataFrame()

    #we need to get the symbols and symbol object, so we can set the name in the dataframe, and also get the fundamental value (ticker = setting name)
    for fundamental in securities:
        ratio =   [fundamental.ValuationRatios.SustainableGrowthRate,
                        fundamental.ValuationRatios.PayoutRatio,
                        fundamental.ValuationRatios.CashReturn,
                        fundamental.ValuationRatios.FCFRatio,
                        fundamental.ValuationRatios.EarningYield,
                        fundamental.ValuationRatios.SalesYield,
                        fundamental.ValuationRatios.BookValueYield,
                        fundamental.ValuationRatios.PEGRatio,
                        fundamental.ValuationRatios.PBRatio,
                        fundamental.ValuationRatios.BuyBackYield,
                        fundamental.ValuationRatios.PricetoEBITDA,
                        fundamental.ValuationRatios.ForwardPERatio,
                        fundamental.ValuationRatios.EVToEBITDA3YrAvgChange,
                        fundamental.ValuationRatios.EVtoFCF,
                        fundamental.ValuationRatios.EVtoEBIT,
                        fundamental.ValuationRatios.EVToForwardEBITDA,
                        fundamental.ValuationRatios.EVToForwardRevenue,
                        fundamental.ValuationRatios.ForwardROE,
                        fundamental.ValuationRatios.ForwardDividend]

        #transpose the dataframe, so it is a long dataframe like in the notebook, set the column names, and concat
        ratio = pd.DataFrame(ratio, index = fundamentals, columns=[fundamental.Symbol])
        ratio = ratio.transpose()
        fundamental_dataframe = pd.concat([ratio, fundamental_dataframe], axis=0)
                         
    #Make the dataframe into a nicely shaped dataframe


    #Append the 2 dataframes
    dataframe = pd.concat([fundamental_dataframe, dataframe], axis=1)

I have tried the following:

  • Appending fundamental data does not work -- makes a copy, and does not merge/concat
  • Making 2 dataframes and merging/concat does not work, half of the new dataframe is Nan
  • Making 1 dataframe with fundamental, and one series, making a new column with the seriesdoes not work either. Returns column is Nan


Anyone have a fix, or something that could solve the problem described above?

Have a good day

Lucas