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!()
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);
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