Hi

I have tried to work out a way to loop over a list of fundamental ratios, and get it nice and smooth into a pandas DataFrame, so I can implement a multivariable linear regression model on the data. My lack of data manipulation skills in pandas unfortunately stops me from making it work. I have worked out a way to do it without a loop, but i takes up a lot of space, and it is not flexible at all (see code). 

  1. qb = QuantBook()
  2. from sklearn.decomposition import FactorAnalysis
  4. tickers = ("AAPL", "MSFT")
  5.            
  6. symbols = [qb.AddEquity(ticker, Resolution.Daily).Symbol for ticker in tickers]
  10. start_time = datetime(2017, 1, 2)
  11. end_time = datetime(2020, 1, 2)
  13. funda_start = datetime(2020, 1, 1)
  14. funda_end = datetime(2020, 1, 2)
  18. fundamentals = ["ValuationRatios.SustainableGrowthRate", "ValuationRatios.PayoutRatio"]
  20. pe_ratios = qb.GetFundamental(symbols, "ValuationRatios.SustainableGrowthRate", funda_start, funda_end)
  22. pay_ratio = qb.GetFundamental(symbols, "ValuationRatios.PayoutRatio", funda_start, funda_end)
  26. dataframe = pd.concat([pe_ratios, pay_ratio], axis=0)
  27.                       
  28. dataframe = dataframe.transpose()
  29. dataframe.columns = fundamentals
  33. returns = qb.History(symbols, start_time, end_time, Resolution.Daily)['close']
  34. returns = returns.to_frame()
  35. returns.reset_index(["time"], inplace=True)
  36. final_returns = returns.groupby(level=0)
  37. final_returns = final_returns.agg(lambda x: x.iloc[-1]).close
  38. final_returns = final_returns.to_frame()
  41. final_data = pd.concat([dataframe, final_returns], axis=1)
  43. y = final_data.close
  44. X = dataframe
  47. from sklearn import linear_model
  49. regr = linear_model.LinearRegression()
  50. regr.fit(X, y)
  51. print(regr.coef_)
+ Expand

 

The problem I am encountering is that a loop over the list, and appending to a empty DataFrame does not work (see code)

  1. qb = QuantBook()
  3. tickers = ("AAPL", "MSFT")
  4.            
  5. symbols = [qb.AddEquity(ticker, Resolution.Daily).Symbol for ticker in tickers]
  7. start_time = datetime(2017, 1, 2)
  8. end_time = datetime(2020, 1, 2)
  10. funda_start = datetime(2020, 1, 1)
  11. funda_end = datetime(2020, 1, 2)
  13. fundamentals = ["ValuationRatios.SustainableGrowthRate", "ValuationRatios.PayoutRatio"]
  15. ratios = pd.DataFrame()
  16. for fundamental in fundamentals:
  17.     ratio = qb.GetFundamental(symbols, fundamental, funda_start, funda_end)
  18.     ratio1 = ratio.transpose()
  19.     ratio1.set_axis([fundamental], axis=1)
  20.     ratios.append(ratio1)
  22. ratios
+ Expand

 

I was wondering if any knew how to solve this problem, as it becomes more irritating, the more variables is being used in the program

Have a nice day

Lucas

 

Author

Lucas

February 2022