First Example
Example: calculate summary statistics and monthly returns for each stock
First, generate the simulated data for the example. The script below generates a vector of stock tickers, a vector of months in the first quarter of 2015, a vector of random numbers that are drawn from the distributions of Normal(100,10) and Normal(50,5) as stock prices, and a vector of random numbers that are drawn from the distributions of Normal(2000,400) and Normal(1000,300) as trading volumes.
id=take(`ABC`XYZ, 6).sort!()
time=take(2015.01M..2015.03M,6)
price=norm(100,10,3) join norm(50,5,3)
volume=norm(2000,400,3) join norm(1000,300,3);
Next, construct a table with the 4 vectors above. A table in DolphinDB is very similar to a dataframe in Python.
data=table(id, time, price.round(2) as price, volume.round(0) as volume);
data;
id | time | price | volume |
---|---|---|---|
ABC | 2015.01M | 96.56 | 1785 |
ABC | 2015.02M | 90.07 | 1861 |
ABC | 2015.03M | 88 | 2155 |
XYZ | 2015.01M | 49.95 | 1253 |
XYZ | 2015.02M | 43.19 | 349 |
XYZ | 2015.03M | 47.68 | 890 |
With the simulated data, we can calculate the average, minimum and maximum volume for each stock. The syntax here is the same as the ANSI SQL.
select avg(volume) as avgVolume, min(volume) as minVolume, max(volume) as maxVolume from data group by id;
id | avgVolume | minVolume | maxVolume |
---|---|---|---|
ABC | 1933.666667 | 1785 | 2155 |
XYZ | 830.666667 | 349 | 1253 |
Finally, calculate monthly returns. The context by clause is an innovation of DolphinDB that makes time-series data processing very convenient.
select id, time, eachPre(ratio, price)-1 as ret from data context by id;
id | time | price |
---|---|---|
ABC | 2015.01M | |
ABC | 2015.02M | -0.067212 |
ABC | 2015.03M | -0.022982 |
XYZ | 2015.01M | |
XYZ | 2015.02M | -0.135335 |
XYZ | 2015.03M | 0.103959 |