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 standard 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