2021.12.04 06:23 AM
hi all:
1、how to sql and use the result? for example
select f1 from table1
and than use f1 as variable,like sql procedure select f1 into var from table1 and use var after
2、how to caculate cointegration of two column
3、how to drop tables
q)tables[]
`aggregate_quote`aggregate_quote1`trades
drop aggregate_quote aggregate_quote1 trades
4、is there q lib like talib for finance data
thanks
2021.12.05 05:46 PM
Hi Terry,
Welcome to the KX Community! Great questions👍🏻 Looking forward to see how others approach each of your points.
Kind regards,
David
2021.12.06 05:59 AM
Thanks for your questions!
1. If f1 in your example is a column, you can simply assign this in your select statement e.g.
f1var: select f1 from table1
then f1var will be available to you to use as a local variable
2. I'll need a little more information to answer this question fully for you. By cointegration do you mean combining two columns into one? If so, this will depend the datatypes of the columns.
You can see an example of combining two columns into a single column here: https://code.kx.com/q/basics/qsql/#computed-columns
3. The drop command in SQL is similar to the delete keyword in q. You can delete a table from memory by running something like
tables[]
`aggregate_quote`aggregate_quote1`trades
delete aggregate_quote1 from `.
tables[]
`aggregate_quote`trades
Take care when using this. The delete keyword will only work for removing variables from a namespace and will not delete from a partitioned database, for example. More info here https://code.kx.com/q/ref/delete/
4. I'm not familiar with ta-lib myself, but from a quick google I can see that it is a python library for technical analysis of financial data. We don't have libraries as such for this kind of analysis - financial analysis is really q's bread and butter, with or without use of one of our KX products. But we also have the option to integrate with python in a few different ways: https://code.kx.com/pykdb/comparisons.html
Hope this helps!
Laura
2022.01.13 12:41 AM
thanks Laura :
for cointegration
def coint(
y0,
y1,
trend="c",
method="aeg",
maxlag=None,
autolag="aic",
return_results=None,
😞
"""
Test for no-cointegration of a univariate equation.
The null hypothesis is no cointegration. Variables in y0 and y1 are
assumed to be integrated of order 1, I(1).
This uses the augmented Engle-Granger two-step cointegration test.
Constant or trend is included in 1st stage regression, i.e. in
cointegrating equation.
**Warning:** The autolag default has changed compared to statsmodels 0.8.
In 0.8 autolag was always None, no the keyword is used and defaults to
"aic". Use `autolag=None` to avoid the lag search.
Parameters
----------
y0 : array_like
The first element in cointegrated system. Must be 1-d.
y1 : array_like
The remaining elements in cointegrated system.
trend : str {"c", "ct"}
The trend term included in regression for cointegrating equation.
* "c" : constant.
* "ct" : constant and linear trend.
* also available quadratic trend "ctt", and no constant "nc".
method : {"aeg"}
Only "aeg" (augmented Engle-Granger) is available.
maxlag : None or int
Argument for `adfuller`, largest or given number of lags.
autolag : str
Argument for `adfuller`, lag selection criterion.
* If None, then maxlag lags are used without lag search.
* If "AIC" (default) or "BIC", then the number of lags is chosen
to minimize the corresponding information criterion.
* "t-stat" based choice of maxlag. Starts with maxlag and drops a
lag until the t-statistic on the last lag length is significant
using a 5%-sized test.
return_results : bool
For future compatibility, currently only tuple available.
If True, then a results instance is returned. Otherwise, a tuple
with the test outcome is returned. Set `return_results=False` to
avoid future changes in return.
Returns
-------
coint_t : float
The t-statistic of unit-root test on residuals.
pvalue : float
MacKinnon"s approximate, asymptotic p-value based on MacKinnon (1994).
crit_value : dict
Critical values for the test statistic at the 1 %, 5 %, and 10 %
levels based on regression curve. This depends on the number of
observations.
Notes
-----
The Null hypothesis is that there is no cointegration, the alternative
hypothesis is that there is cointegrating relationship. If the pvalue is
small, below a critical size, then we can reject the hypothesis that there
is no cointegrating relationship.
P-values and critical values are obtained through regression surface
approximation from MacKinnon 1994 and 2010.
If the two series are almost perfectly collinear, then computing the
test is numerically unstable. However, the two series will be cointegrated
under the maintained assumption that they are integrated. In this case
the t-statistic will be set to -inf and the pvalue to zero.
TODO: We could handle gaps in data by dropping rows with nans in the
Auxiliary regressions. Not implemented yet, currently assumes no nans
and no gaps in time series.
References
----------
.. [1] MacKinnon, J.G. 1994 "Approximate Asymptotic Distribution Functions
for Unit-Root and Cointegration Tests." Journal of Business & Economics
Statistics, 12.2, 167-76.
.. [2] MacKinnon, J.G. 2010. "Critical Values for Cointegration Tests."
Queen"s University, Dept of Economics Working Papers 1227.
http://ideas.repec.org/p/qed/wpaper/1227.html
"""
y0 = array_like(y0, "y0")
y1 = array_like(y1, "y1", ndim=2)
trend = string_like(trend, "trend", options=("c", "nc", "ct", "ctt"))
method = string_like(method, "method", options=("aeg",))
maxlag = int_like(maxlag, "maxlag", optional=True)
autolag = string_like(
autolag, "autolag", optional=True, options=("aic", "bic", "t-stat")
)
return_results = bool_like(return_results, "return_results", optional=True)
nobs, k_vars = y1.shape
k_vars += 1 # add 1 for y0
if trend == "nc":
xx = y1
else:
xx = add_trend(y1, trend=trend, prepend=False)
res_co = OLS(y0, xx).fit()
if res_co.rsquared < 1 - 100 * SQRTEPS:
res_adf = adfuller(
res_co.resid, maxlag=maxlag, autolag=autolag, regression="nc"
)
else:
warnings.warn(
"y0 and y1 are (almost) perfectly colinear."
"Cointegration test is not reliable in this case.",
CollinearityWarning,
)
# Edge case where series are too similar
res_adf = (-np.inf,)
# no constant or trend, see egranger in Stata and MacKinnon
if trend == "nc":
crit = [np.nan] * 3 # 2010 critical values not available
else:
crit = mackinnoncrit(N=k_vars, regression=trend, nobs=nobs - 1)
# nobs - 1, the -1 is to match egranger in Stata, I do not know why.
# TODO: check nobs or df = nobs - k
pval_asy = mackinnonp(res_adf[0], regression=trend, N=k_vars)
return res_adf[0], pval_asy, crit
2022.01.13 03:25 AM
Hi @terryyao,
I see you've asked a new thread about cointegration so we can continue our conversation over there: https://community.kx.com/t5/KX-Technology/Johansen-cointegration-test-kdb-implement/td-p/11613
Thanks,
Laura
EMEA
Tel: +44 (0)28 3025 2242
AMERICAS
Tel: +1 (212) 447 6700
APAC
Tel: +61 (0)2 9236 5700
KX. All Rights Reserved.
KX and kdb+ are registered trademarks of KX Systems, Inc., a subsidiary of FD Technologies plc.