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
Lucas
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:
Have a good day
Lucas
Louis Szeto
Hi Lucas,
In order to create a dataframe combining the fundamentals and history data, we'll need
Please find the attached backtest as an example.
Best
Louis
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.
Lucas
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
Varad Kabade
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
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.
To unlock posting to the community forums please complete at least 30% of Boot Camp.
You can continue your Boot Camp training progress from the terminal. We hope to see you in the community soon!