Linear Regression Line & R2'd Help

[wmsifl]

Hi, All - I want to work with linear regression lines and R-squared output on forex price data and I'm having trouble understanding what the "Known X's" input is that the function requires.....I can see where the "Known Y's" would be the price series I'm looking at...in this case, the closes for a specific currency pair at the end of each 24-hour time session, but I can't figure out the "X's".....R-squared requires the same data so if I can figure it out for the regression line then I'm set...

I appreciate any help!

Thanks,

Rick

 

[shg]

It would be the dates associated with the prices, if what you're looking at is a time series.

 

[wmsifl]

Thx, Shg - just to clarify then.....I would list the specific dates in a series of cells that correspond to the prices and then point to that range of cells as the "Known X's"?

I really appreciate the quick reply!

Rick

 

[shg]

Well, maybe. What are are you trying to regress? Are you looking to do the equivalent of plotting the price vs date and adding a linear trendline? That doesn't sound real likely to me.

 

[wmsifl]

Well, I have a series of 24-hour closing forex prices over a period of 30 days that I want to regress from a bar chart of prices...so, it would be 30 different closing prices on the chart that, taken alone, would look like a scatter plot over the 30-day period that I want to draw a regression line through.....

Is that not possible?

Thanks again!

 

[shg]

If the question is, "What is the trend of closing prices over this 30-day period", then select a 3 x 2 range (e.g., A1:B3) and array-enter

=LINEST(prices, dates, , TRUE)

R2 will appear in A3. See Help for LINEST.

 

[wmsifl]

Thx, Shg - appreciate the direction....the reason I wanted to use a Slope function was that I was hoping to define a "flat" vs. "rising/falling" slope of the trendline with a numerical boundary that could then be referenced against the R2 number to tell me whether the trend line was real or not so that trades could then either be placed in the direction of the trend or counter to the trend.....

ie....if Slope (over 30 periods) > -1 and Slope < 1 and R2 >= .80 then trend = "flat" , which is saying that the recent price trend is flat with R2 confirming it with an 80% or better reading (the slope settings would need to be tested to define whether a -1 to 1 range is right or not, but it's a starting point)....counter trend trades would be fine in this environment as the market is just bouncing around.....

then...if Slope < -1 and R2 >= .80, then trend = downtrend.....same rationale as above...this would set up short trade positions....

then if Slope > 1 and R2 >= .80, then trend = uptrend.....same rationale as above...this would set up long trade positions....

So, to summarize, I wanted to use the Slope function to help me define a trending (or not) condition that could be confirmed by R2. Looks like the LINEST function would help me draw the line, but I'm not sure it will help me get the data I was looking for that could then be programmed without having to do separate visual and manual inspections of price charts. I was hoping this could substantially reduce setup time daily in spotting candidates that are actually setup correctly for the proper trend conditions to trade or not.

Does that make sense

Thanks, Rick

 

[shg]

Using LINEST, slope is the value in A1.

If you have a further question, I don't understand what it is.

 

[wmsifl]

Thx, Shg - actually that should do it....since the slope would be numerically defined as you mentioned. Then it would just be a matter of defining the range for slope that constitutes "flat" vs. a more positive slope for uptrends and negative slope for downtrends.

Let me give that a go and if I have any problems I might come back to you. I really appreciate the help!!

 

[shg]

Good luck, holler back if you need help.