Working Money magazine.  The investors' magazine. Advantage



Article Archive | Search | Subscribe/Renew | Login | Free Trial | Forgot ID?


Setting Up an Excel Spreadsheet for the Forecast Oscillator

10/26/00 03:49:17 PM
by Dennis D. Peterson

This article is reprinted from Technical Analysis of Stocks & Commodities magazine, Volume 10, Issue 5 to supplement my recent article "Forecast Oscillator".

Security:   N/A
Position:   N/A

Following is an example of setting up an Excel spreadsheet for creating a forecast oscillator using a five-day linear regression for tomorrow's close.

Figure 1, Column A, lists the dates and Column D lists the closing prices (the Y variables) used for the forecasted close. Cells El through E5 are the constants used for the X variables.

Figure 1: Sample Excel spreadsheet for creating a forecast.
An Excel spreadsheet has built-in formulas to calculate the estimated coefficients m and c of a linear regression and the r-squared value. For example, the formula in cell E8 is:


This formula is calculating coefficient c, which is our constant based on the last five days' data. Cell F8 returns the value of the slope m:


The formula for cell G8 returns the r-squared:


The key is the last value showed in the formula before the final parenthesis, as this instructs the spreadsheet to either return c, m, or r-squared.

Once you have the first set of coefficients, you can forecast tomorrow's close. This is performed in cell

=E8 +F8*6

Finally, the formula for the forecast oscillator is listed in I9:



Dennis D. Peterson

Market index trading on a daily basis.

Title: Staff Writer
Company: Technical Analysis, Inc.
Address: 4757 California Ave SW
Seattle, WA 98116-4499
Phone # for sales: 206 938 0570
Fax: 206 938 1307
E-mail address:

Traders' Resource Links
Charting the Stock Market: The Wyckoff Method -- Books -- Online Trading Services Advantage -- Online Trading Services
Technical Analysis of Stocks & Commodities -- Publications and Newsletters
Working Money, at -- Publications and Newsletters Advantage -- Publications and Newsletters
Professional Traders Starter Kit -- Software

Click here for more information about our publications!

Comments or Questions? Article Usefulness
5 (most useful)
1 (least useful)



S&C Subscription/Renewal

Request Information From Our Sponsors 

DEPARTMENTS: Advertising | Editorial | Circulation | Contact Us | BY PHONE: (206) 938-0570

PTSK — The Professional Traders' Starter Kit
Home — S&C Magazine | Working Money Magazine | Advantage | Online Store | Traders’ Resource
Add a Product to Traders’ Resource | Message Boards | Subscribe/Renew | Free Trial Issue | Article Code | Search

Copyright © 1982–2020 Technical Analysis, Inc. All rights reserved. Read our disclaimer & privacy statement.