Calculating the corr. coefficient between returns of portfolios in EXCEL I want to determine the correlation between the returns of a portfolio with returns of the market (suitable index). To find how the portfolio performs in bear resp. bull markets, I want to calculate the correlation coeffients over different periods when the market is in decline or in rise  (is this the correct antonym for decline/recess?).
So in my first attempt I just took the returns (where 20% is 0.20) from the market and the portfolio for 4-months period in between the longer period of rise, and then calculated the correlation coefficient. But what I found was that the they had strong negative correlation (~-0.4), although both the market and the index had strictly positive returns during the whole period. Doesn't this seem a bit misleading? Shouldn't they be positively correlated instead?
So instead I created the timeseries of how the values of the portfolio resp. market changed over time (normalized), then calculated the correlation coefficients over the same period and found that the correlation coefficient was ~0.9. This seems more reasonable.
My question is what is the correct way to determine the corr. coeffient in this case? Is it even acceptable to do this. Is it more suitable to look at the rolling correlation instead and try to see how it changes over time. 
Note: Excel was used to calculate the corr. coeffient.
edit:
So the the market is just the index normalized so that its value is 1 at time one. In other words all time points are divided by the first value of the time series.
The portfolio is just calculated by adding the returns of the strategy for every period. The value of this time series starts at one aswell. At time t the value of the portfolio is
$1+r_{1}+r_2+r_3 + ... + r_t$ etc. (Notice I do not want to calculate it by continous compounding since it is not reasonable for the strategy)
 A: To answer the question: Can the correlation of returns be negative, if both had positive returns?
Yes. The correlation coefficient can be understood as a normalized covariance, right?  
So the covariance is a measure of how both values move in relation to their expected value (here - the mean).
So it depends on this if the covariance is positive or negative.
To put it differently with $X$ as market return and $Y$ as portfolio return.
$$V(X,Y) = E[ (X-E[X]) (Y-E[Y] ]$$
In your case you probably used this estimator
$$ \hat{V}(X,Y)=\frac{1}{N-1}\sum_{i=1}^{N}\left(  x_{i}-\bar{x} \right)  \left( y_{i}-\bar{y} \right)$$
Where $\bar{x}$ and $\bar{y}$ are the respective arithmetic means.
Now obviously if both have positive returns, yet one portfolio deviates away below from its mean while the other deviates away above its mean, you get a negative number in that sum.
So even though both might be positive in absolute values, it depends on where they are moving.
Finally, if your covariance is negative, well what is your correlation coeefficient?
As for your second question: Please detail exactly how you calculated the time series and normalization because I can't tell you if what you did in either case was correct. Best post the formulas to be sure.
