Mid Point Over Period
Returns the midpoint value calculated from closing prices over a specified period. This represents the center of the price range.
Calculation
Midpoint = (Highest Close + Lowest Close) / 2
This is similar to the Donchian Channel midline but using closing prices.
Uses
- Support/Resistance: The midpoint can act as a dynamic support or resistance level
- Mean Reversion: Price tends to return to the midpoint after extremes
- Trend Following: Price above midpoint suggests uptrend, below suggests downtrend
Parameters
| Parameter | Description | Default |
|---|---|---|
| Symbol | Stock ticker symbol | Required |
| Days | Number of periods | 14 |
| StartDate | Calculate as of this date | Today |
Examples
=MidPointOverPeriod("AAPL")=MidPointOverPeriod("AAPL", "20")=MidPointOverPeriod("MSFT", "14")=MidPointOverPeriod("SPY", "52")=MidPointOverPeriod("AAPL", "14", DATE(2024,1,15))=MidPointOverPeriod(A1)When to Use
- Identify the center of a trading range
- Mean reversion trading strategies
- Support/resistance level identification
- Donchian-style channel analysis
- Simple trend identification
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need weighted average | SimpleMovingAverage() or ExponentialMovingAverage() |
| Need intraday high/low midpoint | Different calculation needed |
| Need momentum oscillator | RelativeStrengthIndex() |
| Need volatility measure | AverageTrueRange() |
Common Issues & FAQ
Q: Why is MidPointOverPeriod returning "NA"? A: Check that:
- The symbol is valid and actively traded
- There is sufficient historical data for the period
Q: How does this differ from SMA? A: SMA calculates the arithmetic mean of all closing prices. Midpoint only uses the highest and lowest closes, making it less sensitive to daily fluctuations but more responsive to range extremes.
Q: When is midpoint useful? A: Midpoint is useful for range-bound trading and identifying the equilibrium price within a channel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today
▶
How does MarketXLS work?