Getting returns merged with fundamental dataframe

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       
+ Expand
- Collapse

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)
+ Expand
- Collapse

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

The material on this website is provided for informational purposes only and does not constitute an offer to sell, a solicitation to buy, or a recommendation or endorsement for any security or strategy, nor does it constitute an offer to provide investment advisory services by QuantConnect. In addition, the material offers no opinion with respect to the suitability of any security or specific investment. QuantConnect makes no guarantees as to the accuracy or completeness of the views expressed in the website. The views are subject to change, and may have become unreliable for various reasons, including changes in market conditions or economic circumstances. All investments involve risk, including loss of principal. You should consult with an investment professional before making any investment decisions.

from AlgorithmImports import *
from datetime import timedelta
import pandas as pd
# endregion
class CalmTanLlama(QCAlgorithm):
def Initialize(self):
self.SetStartDate(2020, 10, 15) # Set Start Date
self.SetEndDate(2020, 11, 20)
self.SetCash(100000) # Set Strategy Cash
self.SetBenchmark("SPY")
self.UniverseSettings.Resolution = Resolution.Hour
self.SetBrokerageModel(BrokerageName.InteractiveBrokersBrokerage, AccountType.Margin)
self.numCoarse = 200
self.numFineMarket = 10
self.numFine = 10
self.rebalancingFunc = Expiry.EndOfMonth
self.returnLookback = timedelta(weeks = 104)
self.returnResolution = Resolution.Daily
self.nextRebalance = None
self.AddUniverse(self.CoarseUniverse, self.SelectFine)
self.SetAlpha(ConstantAlphaModel(InsightType.Price, InsightDirection.Up, timedelta(hours=5)))
self.SetPortfolioConstruction(NullPortfolioConstructionModel())
self.SetExecution(NullExecutionModel())
self.SetRiskManagement(NullRiskManagementModel)
def CoarseUniverse(self, coarse):
if self.nextRebalance is not None and self.Time < self.nextRebalance:
return Universe.Unchanged
self.nextRebalance = self.rebalancingFunc(self.Time)
selected = sorted([x for x in coarse if x.HasFundamentalData and x.Price > 5],
key = lambda x: x.DollarVolume, reverse=True)
return [x.Symbol for x in selected[:self.numCoarse]]
def SelectFine(self, fine):
sortedMarketCap = sorted(fine, key = lambda x: x.MarketCap, reverse=True)[:self.numFineMarket]
#Get the results of the linear regression analysis
linearRegressionResults = self.AppendDataFrames(self, sortedMarketCap, self.returnLookback, self.numFine)
##
#Do something with the dataframe
##
return [x.Symbol for x in sortedMarketCap]
def AppendDataFrames(self, algorithm, symbols, returnLookback, maxstocks):
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)
#
#Do something with the dataframe
#
return 0

+ Expand

Hi Louis

I appriciate the help a lot. I will port this into the alpha module, seems straightforward.

A little follow-up question if you dont mind. Why does this method not work in the FineUniverse?

Have a good day

Lucas

Lucas INVESTOR

Lucas Bjerre Rasmussen | April 2022

Here is a quick example algo I coded up, that demonstrate the problem. Furthermore, I am also getting an trying to dynamically access a method that does not exist error that referes to QuantConnect.Data.UniverseSelection.SecurityChanges, but I do not know how

Here is the code:

from AlgorithmImports import *
from datetime import timedelta
import pandas as pd
# endregion
class CalmTanLlama(QCAlgorithm):
    def Initialize(self):
        self.SetStartDate(2020, 10, 15)  # Set Start Date
        self.SetEndDate(2020, 11, 20)
        self.SetCash(100000)  # Set Strategy Cash
        self.SetBenchmark("SPY")
        self.UniverseSettings.Resolution = Resolution.Hour
        self.SetBrokerageModel(BrokerageName.InteractiveBrokersBrokerage, AccountType.Margin)
        
        self.numCoarse = 200
        self.numFineMarket = 10
        self.numFine = 10
        self.rebalancingFunc = Expiry.EndOfMonth
        self.returnLookback = timedelta(weeks = 104)
        self.returnResolution = Resolution.Daily
        self.nextRebalance = None
        self.AddUniverse(self.CoarseUniverse, self.SelectFine)
        self.SetAlpha(ConstantAlphaModel(InsightType.Price, InsightDirection.Up, timedelta(hours=5)))
        self.SetPortfolioConstruction(NullPortfolioConstructionModel())
        self.SetExecution(NullExecutionModel())
        self.SetRiskManagement(NullRiskManagementModel)
    def CoarseUniverse(self, coarse):
        if self.nextRebalance is not None and self.Time < self.nextRebalance:
            return Universe.Unchanged
        self.nextRebalance = self.rebalancingFunc(self.Time)
        
        selected = sorted([x for x in coarse if x.HasFundamentalData and x.Price > 5], 
                            key = lambda x: x.DollarVolume, reverse=True)
        
        return [x.Symbol for x in selected[:self.numCoarse]]
    def SelectFine(self, fine):
        
        sortedMarketCap = sorted(fine, key = lambda x: x.MarketCap, reverse=True)[:self.numFineMarket]
        #Get the results of the linear regression analysis
        linearRegressionResults = self.AppendDataFrames(self, sortedMarketCap, self.returnLookback, self.numFine)
        ##
        #Do something with the dataframe
        ##
        return [x.Symbol for x in sortedMarketCap]
    def AppendDataFrames(self, algorithm, symbols, returnLookback, maxstocks):
    
        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)
        #
        #Do something with the dataframe
        #
        return 0
+ Expand
- Collapse

Upvote

Varad Kabade INVESTOR

April 2022

Hi Lucas,
We can use the same inside the fine filter. We need to use the fine fundamental object to get the fundamentals.
Best,
Varad Kabade

Platform

Radically Open-Source Algorithmic Trading Engine

Join Our Discord Channel

Quarterly Open-Source Trading Competition

Draft Discussions

Bookmarked Discussions

SEARCH DISCUSSIONS

373,300 Quants.

VOTE FOR UPCOMING FEATURES

JOIN OUR Community MAILING LIST

IN THIS RESEARCH

PARTICIPANTS

Actions

Join QuantConnect for Free