I have a table with timestamp / symbol where each row has an incremental timestamp and a random symbol.
q)sym:`aapl`tsla`aapl`msft`tsla`qcom`msft`tsla`aapl`qcom
::
q)t:([] ts:.z.p+til count sym;sym)
::
q)t
ts sym
----------------------------------
2025.02.09D14:43:43.054056807 aapl
2025.02.09D14:43:43.054056808 tsla
2025.02.09D14:43:43.054056809 aapl
2025.02.09D14:43:43.054056810 msft
2025.02.09D14:43:43.054056811 tsla
2025.02.09D14:43:43.054056812 qcom
2025.02.09D14:43:43.054056813 msft
2025.02.09D14:43:43.054056814 tsla
2025.02.09D14:43:43.054056815 aapl
2025.02.09D14:43:43.054056816 qcom
I want to add a column with random prices whereby each row is within +/-(N*delta_unit) from the last price for that symbol. For example, suppose the starting price for aapl is $200.00 and delta_unit is 0.01. If N is 2, each subsequent price for aapl should be within $0.02 of the last price:
ts sym
----------------------------------
2025.02.09D14:43:43.054056807 aapl $200.00
2025.02.09D14:43:43.054056808 tsla
2025.02.09D14:43:43.054056809 aapl $199.98
2025.02.09D14:43:43.054056810 msft
2025.02.09D14:43:43.054056811 tsla
2025.02.09D14:43:43.054056812 qcom
2025.02.09D14:43:43.054056813 msft
2025.02.09D14:43:43.054056814 tsla
2025.02.09D14:43:43.054056815 aapl $199.99
How can I generate random prices like this (maybe given a map from symbol to starting price)?
I have a table with timestamp / symbol where each row has an incremental timestamp and a random symbol.
q)sym:`aapl`tsla`aapl`msft`tsla`qcom`msft`tsla`aapl`qcom
::
q)t:([] ts:.z.p+til count sym;sym)
::
q)t
ts sym
----------------------------------
2025.02.09D14:43:43.054056807 aapl
2025.02.09D14:43:43.054056808 tsla
2025.02.09D14:43:43.054056809 aapl
2025.02.09D14:43:43.054056810 msft
2025.02.09D14:43:43.054056811 tsla
2025.02.09D14:43:43.054056812 qcom
2025.02.09D14:43:43.054056813 msft
2025.02.09D14:43:43.054056814 tsla
2025.02.09D14:43:43.054056815 aapl
2025.02.09D14:43:43.054056816 qcom
I want to add a column with random prices whereby each row is within +/-(N*delta_unit) from the last price for that symbol. For example, suppose the starting price for aapl is $200.00 and delta_unit is 0.01. If N is 2, each subsequent price for aapl should be within $0.02 of the last price:
ts sym
----------------------------------
2025.02.09D14:43:43.054056807 aapl $200.00
2025.02.09D14:43:43.054056808 tsla
2025.02.09D14:43:43.054056809 aapl $199.98
2025.02.09D14:43:43.054056810 msft
2025.02.09D14:43:43.054056811 tsla
2025.02.09D14:43:43.054056812 qcom
2025.02.09D14:43:43.054056813 msft
2025.02.09D14:43:43.054056814 tsla
2025.02.09D14:43:43.054056815 aapl $199.99
How can I generate random prices like this (maybe given a map from symbol to starting price)?
Share Improve this question asked yesterday AlexandrosAlexandros 2,2331 gold badge21 silver badges32 bronze badges1 Answer
Reset to default 1You may want to tweak this to your liking but here's a starting point.
genpx below takes delta_unit, N and sym. Starting price from the dictionary is the first value which is then joined with a list of n-1 of price changes. n?-1 1 generates +/- values multiplied by a random N value (e.g. 5). This is multiplied by the delta_unit input (e.g. 0.01) and finally sums to get the final result of up/down moving prices.
/ starting prices
sp:distinct[sym]!count[distinct sym]?300f
/ gen price function
genpx:{[delta_unit;N;s] n:-1+count s; sums sp[last s],delta_unit*(n?-1 1)*n?N}
q)update px:genpx[0.01;5;sym] by sym from t
ts sym px
-------------------------------------------
2025.02.09D15:58:46.787418000 aapl 117.8257
2025.02.09D15:58:46.787418001 tsla 155.1273
2025.02.09D15:58:46.787418002 aapl 117.8057
2025.02.09D15:58:46.787418003 msft 154.7939
2025.02.09D15:58:46.787418004 tsla 155.1473
2025.02.09D15:58:46.787418005 qcom 121.9993
2025.02.09D15:58:46.787418006 msft 154.7839
2025.02.09D15:58:46.787418007 tsla 155.1873
2025.02.09D15:58:46.787418008 aapl 117.8157
2025.02.09D15:58:46.787418009 qcom 121.9793
q)update px:genpx[0.01;5;sym] by sym from t
ts sym px
-------------------------------------------
2025.02.09D15:58:46.787418000 aapl 117.8257
2025.02.09D15:58:46.787418001 tsla 155.1273
2025.02.09D15:58:46.787418002 aapl 117.8357
2025.02.09D15:58:46.787418003 msft 154.7939
2025.02.09D15:58:46.787418004 tsla 155.1173
2025.02.09D15:58:46.787418005 qcom 121.9993
2025.02.09D15:58:46.787418006 msft 154.7939
2025.02.09D15:58:46.787418007 tsla 155.1273
2025.02.09D15:58:46.787418008 aapl 117.8157
2025.02.09D15:58:46.787418009 qcom 121.9893