準備計算數據
好，我需要請求數據來計算係數，並為用戶提供一個表格結構，這樣他們可以直接粘貼數據然後使用公式進行計算。還要遵循格式規範：使用標題、水平分隔線、以及標粗的列表標題。當涉及數學公式時，記得使用 LaTeX 格式。這樣一來，我就能確保一切都準備好了！
What I need to solve your coefficients
- Data ranges: Please share the exact ranges for (x) (time/index) and (y) (price) you want fitted.
- Window choice: Length: number of points per fit; mode: rolling window or full-sample.
- Scaling: Confirm if we center/scale (x) (recommended) or use raw (x).
If you give me these, I’ll return your full set of coefficients (\beta_0 \ldots \beta_8) directly.
The polynomial trend8 model
Model:
[
y = \beta_0 + \beta_1 z + \beta_2 z2 + \cdots + \beta_8 z8
]
Center-scale (x) to (z):
[
z = \frac{x - \mu_x}{\sigma_x}
]
This stabilizes coefficients and reduces multicollinearity.
Excel-only setup (fastest to get coefficients)
- Prepare columns:
- z: ((x - \text{AVERAGE}(x))/\text{STDEV.S}(x))
- z1 … z8: create eight columns for powers.
- Get coefficients:
- Select 9 horizontal cells and enter:
=LINEST(y_range, z1:z8_range, TRUE, FALSE)
- This returns (\beta_8, \ldots, \beta_1, \beta_0) in one row.
- Trend values:
- Compute (\hat{y} = \beta_0 + \beta_1 z + \cdots + \beta_8 z^8) in a new column to plot.
Tip: Use a rolling window (e.g., last 60–120 bars) to avoid overfitting and Runge effects.
VBA helper (drop-in to produce β0…β8)
- Coefficients function (paste into a standard module):
Function Trend8Coeffs(yRange As Range, xRange As Range) As Variant
Dim ws As WorksheetFunction: Set ws = Application.WorksheetFunction
Dim n As Long, i As Long: n = yRange.Rows.Count
Dim mu As Double, sd As Double: mu = ws.Average(xRange): sd = ws.StDev_S(xRange)
Dim z() As Double: ReDim z(1 To n)
For i = 1 To n: z(i) = (xRange.Cells(i, 1).Value - mu) / sd: Next i
Dim Xvar As Variant, Yvar As Variant, p As Long
ReDim Xvar(1 To n, 1 To 8)
ReDim Yvar(1 To n, 1 To 1)
For i = 1 To n
Yvar(i, 1) = yRange.Cells(i, 1).Value
Dim zp As Double: zp = z(i)
For p = 1 To 8
If p = 1 Then
Xvar(i, p) = zp
Else
zp = zp * z(i)
Xvar(i, p) = zp
End If
Next p
Next i
Trend8Coeffs = ws.LinEst(Yvar, Xvar, True, False) ' returns β8..β1, β0
End Function
Function Trend8Value(xVal As Double, coeffs As Range, mu As Double, sd As Double) As Double
Dim z As Double: z = (xVal - mu) / sd
Dim yhat As Double: yhat = coeffs.Cells(1, 9).Value ' β0
Dim p As Integer, zpow As Double: zpow = z
For p = 1 To 8
yhat = yhat + coeffs.Cells(1, 9 - p).Value * zpow
zpow = zpow * z
Next p
Trend8Value = yhat
End Function
- Use:
- Coeffs:
=Trend8Coeffs(Y_window, X_window) → place across 9 cells (β8…β1, β0).
- Trend:
=Trend8Value(X_i, coeffs_row, mu, sd) to plot fitted line.
Direct matrix form (if you want the full system)
Design matrix:
[
X =
\begin{bmatrix}
1 & z_1 & z_12 & \cdots & z_18 \
\vdots & \vdots & \vdots & & \vdots \
1 & z_n & z_n2 & \cdots & z_n8
\end{bmatrix},\quad
\beta =
\begin{bmatrix}
\beta_0 \ \beta_1 \ \vdots \ \beta_8
\end{bmatrix}
]
Normal equations:
[
(X^{\top}X),\beta = X^{\top}y
]
Solve:
[
\beta = (X^{\top}X){-1}X{\top}y
]
In practice, use QR or SVD to improve stability; in Excel, LINEST already does a robust solve.
Guardrails to keep coefficients meaningful
- Short windows: Fit on the most recent 60–120 bars to avoid boundary oscillations.
- Center-scale: Always use (z), not raw (x).
- Compare orders: Plot 4th, 6th, and 8th together; if 8th diverges, prefer 6th.
- Versioning: Save each coefficient set per window time to archive your “章法的神經”。
Next step
- If you share your x/y ranges (e.g., last 80 bars of 15-minute 00663L), I will compute and return your exact (\beta_0 \ldots \beta_8) here.
- If you prefer a template, I can provide an Excel sheet layout with formulas ready to paste your data.
