Generate two negatively correlated data in excel Let's say that we have  two prices that are negatively correlated to each other, for instance  we have  price  $p_1$  and we want to generate  negatively correlated  price  $p_2$ with the following formula
$$p_2=k p_1+b\varepsilon$$
where  $k$ is correlation coefficient, $b$ is noise level and  $\epsilon$ is noise, but the problem is that for negative correlation $k$ must be less then zero right? but in this case price became negative which is non logical (every price must be   non-negative number), so what should I  do? Instead of 
$$p_2=kp_1+b\varepsilon$$
should I use
$$p_2=a-kp_1+b \varepsilon \text{ ?}$$
where $a$ is intercept 
Thanks in advance
 A: Yes, you can use
$$p_2 = a+kp_1+b\epsilon,$$
where $k$ is negative, but this might still give you negative prices: If the noise $\epsilon$ happens to be so large in the negative direction that $a+kp_1+b\epsilon<0$, then you get a negative price.
This is not a feature of this particular model. It occurs also for the model $p_2 = kp_1+b\epsilon$, even when $k$ is positive, since the noise can take any value, and make the expression negative (if this often happens is another matter, it will depend on your choice of $a$ and $b$, but it will always happen with some positive probability).
What I've seen in practice is to cap the price at some minimum value, e.g. you can use a model
$$p_2 = \max(a+kp_1+b\epsilon,L),$$
where $L\geq 0$ is some lower bound that you do not want your price to fall below.
A: Adding a constant to $p_2$ will not change the correlation with $p_1$.  So, you can feel free do that to keep $p_2 > 0$.  However, $k$ is not exactly the correlation coefficient.
If $\sigma^2_1$ is the variance of $p_1$.  Then $k \sigma^2_1$ is the covariance of $p_1$,$p_2$.  
$$\sigma^2_2 = k^2 \sigma^2_1 + \epsilon^2\sigma^2_b$$
$$\rho = \frac{k\sigma_1}{\sigma_2}$$
