In DolphinDB, how to calculate stock residual return based on a table using the ols function?

Eva Gao 0
Viewed 13

I have a table “clean_factor“ where the column “y“ indicates the stock returns and the subsequent columns indicate factor exposures. How do I calculate the daily residual return of each stock with the ols function?

date       code   y                 101               102               103               104               ...
---------- ------ ----------------- ----------------- ----------------- ----------------- ----------------- ---
2022.01.01 600000 0.805062032770366 0.110651090508327 0.402379305101931 0.867454449646175 0.797910543624312 ...
2022.01.01 600001 0.868045383365825 0.399047139566392 0.548971236450598 0.18031427077949  0.481457493035123 ...
2022.01.01 600002 0.479283686028793 0.309550857404247 0.711831445805729 0.223933940287679 0.25199460145086  ...
2022.01.01 600003 0.881916775833815 0.760919136460871 0.241510216845199 0.400323236128315 0.459630751749501 ...
2022.01.01 600004 0.051689573796466 0.460252749500796 0.477046215441078 0.318984656594694 0.112727724015713 ...
2022.01.01 600005 0.896218206034973 0.918168989010155 0.627255809959024 0.598677115282044 0.657469752011821 ...
2022.01.01 600006 0.722626336384565 0.982886681798846 0.22556566586718  0.743761736899614 0.740151216043159 ...
2022.01.01 600007 0.904315704014152 0.726520796073601 0.479593057185411 0.503981933696196 0.778909109765664 ...
2022.01.01 600008 0.676614116411656 0.103336290689185 0.482191656483337 0.819906129734591 0.92595730512403  ...
2022.01.01 600009 0.477163514122367 0.207688418217003 0.167253567604348 0.207000466994941 0.564272327115759 ...
2022.01.02 600000 0.622801147401333 0.877423054305837 0.998785990988836 0.743025538977236 0.478557676076889 ...
2022.01.02 600001 0.68666562391445  0.391971884528175 0.782422672025859 0.531062707072124 0.590716102859005 ...
2022.01.02 600002 0.649861810961738 0.952449424890801 0.461862253025174 0.512355053797364 0.757486434420571 ...
2022.01.02 600003 0.987731461878866 0.159808385651559 0.043475477024913 0.938050619326532 0.812900307588279 ...
2022.01.02 600004 0.215495072305202 0.525863674702123 0.353840877767652 0.058366338023916 0.584426566492766 ...
2022.01.02 600005 0.191072241170332 0.862271037884057 0.097657802747563 0.892931805225089 0.349999474361539 ...
2022.01.02 600006 0.491893862374127 0.747894760686904 0.044482096331194 0.290295254671946 0.90357043966651  ...
2022.01.02 600007 0.97417120449245  0.91452497872524  0.03118087281473  0.850948303472251 0.24491234915331  ...
2022.01.02 600008 0.850179589819163 0.566376116126776 0.051887998823076 0.219384315190837 0.925669034477323 ...
2022.01.02 600009 0.48799466015771  0.263422361109406 0.491781027056277 0.868601131252944 0.284168284852058 ...
2022.01.03 600000 0.758049110416323 0.026934095425531 0.981129284482449 0.123908579582348 0.023687394568697 ...
2022.01.03 600001 0.598079953342676 0.370341445319355 0.813326318282634 0.200777166290209 0.151706877164543 ...
2022.01.03 600002 0.027243424672633 0.429365777643397 0.7431835308671   0.953405884327367 0.819175065029413 .

This is the script I used. For each date, perform ols calculation on the “Y“ (stock returns) column and the factor exposure columns, then extract the values for “Residual“ from the result:

select date,code,ols(y, clean_factor.colNames()[3:], true, 2)['Residual'] from clean_factor context by date

The following error is raised:

The dimension of dependent doesn't match the dimension of independent factors.' script: '

metaprogramming dolphindb least-squares group-by sql
1 Answers

The error occurred because you passed a vector of strings to the parameter X of the function ols (see syntax below), but the strings are not recognized as references to the corresponding columns in the table "clean_factor". That’s why the error suggests that the dimensions of the first two parameters don’t match.

ols(Y, X, [intercept=true], [mode=0])

With only a few columns participating in the calculation, we can write a SQL query for stock residual calculation and specify all the columns.

For demonstration purpose, we simulate the following data:

x1=1 3 5 7 11 16 23 1 3 5 7 11 16 23
x2=2 8 11 34 56 54 100 2 8 11 34 56 54 100
y=0.1 4.2 5.6 8.8 22.1 35.6 77.2 0.1 4.2 5.6 8.8 22.1 35.6 77.2
date=take(2022.01.03,7).append!(take(2022.01.04,7))
code=take(symbol(string(600001..600007)),14);
clean_factor=table(date,code,y,x1 as `11,x2 as `12)

In DolphinDB, column names containing special characters or starting with numbers must be wrapped in double quotes with an underscore prefix in SQL queries, e.g., ''11'',''12''. As the factors all start with numbers, write the query as follows:

select date,code,ols(y,matrix(_"11",_"12"),1,2)[`Residual] as Residual from clean_factor context by date

As the stock data is indexed in time order (time series data), we must group the records by time before calculating the residuals of each day. This is why we use the SQL keyword context by to group the data by date. context by is a unique feature in DolphinDB. For more information, see DolphinDB documentation (https://dolphindb.com/help/SQLStatements/contextBy.html).

Output:

date       code   Residual
---------- ------ ------------------
2022.01.03 600001 6.525446770493835
2022.01.03 600002 4.223774428465647
2022.01.03 600003 -0.383487374226408
2022.01.03 600004 -5.820152772492698
2022.01.03 600005 -6.638199254336197
2022.01.03 600006 -6.907387053168563
2022.01.03 600007 9.00000525526498
2022.01.04 600001 6.525446770493835
2022.01.04 600002 4.223774428465647
2022.01.04 600003 -0.383487374226408
2022.01.04 600004 -5.820152772492698
2022.01.04 600005 -6.638199254336197
2022.01.04 600006 -6.907387053168563
2022.01.04 600007 9.00000525526498

However, when there are tens or hundreds of columns involved in the calculation, it would be difficult to write out all the column names as in the above query. In this case, we can use metaprogramming (https://dolphindb.com/help/Objects/Metaprogramming.html) for dynamic SQL query generation:

yColName = "y"
xColNames = clean_factor.colNames()[3:]
residual = makeCall(member,makeCall(ols, sqlCol(yColName), makeUnifiedCall(matrix, sqlCol(xColNames)), true, 2), `Residual)
selects = [<date>, <code>, sqlColAlias(residual, "residual")]
sql(selects, clean_factor, groupBy=<date>, groupFlag=0).eval();

With the above script, you can also get the stock residual returns. Specifically, (1) sqlCol(yColName) generates the metacode for selecting the “y“ column, which will be passed to the parameter Y of ols; (2) makeUnifiedCall(matrix, sqlCol(xColNames)) converts the factor columns to a matrix as the parameter X of ols; (3) Finally, with makeCall, a dynamic call to ols is made.

In this example, the intercept parameter of ols is set to true. The parameter mode is specified as “2“ which indicates that the output would be a dictionary. Use the functions makeCall and member to extract the value of “Residual“ from the dictionary with ".Residual" or ['Residual']. Finally, use the function sql to dynamically generate a SQL query for stock residual returns.

Money0
Related