Advanced Automated Price Pattern Analysis Tool with Trend Context and Custom Filters

Request for Advanced Historical OHLC Data Analysis Tool (Excel VBA / Python)

1. Project Title:

Advanced Automated Price Pattern Analysis Tool with Trend Context and Custom Filters

2. Project Goal:

Create a robust program/script that will perform in-depth statistical analysis of historical OHLC (Open, High, Low, Close) data provided by the user from MetaTrader 5 (MT5). The tool will identify recurring price patterns across various timeframes, calculate potential profit/loss in pips/points, analyze wick/shadow properties, determine the context of "clean" and "dirty" trends (before and after the pattern), and visualize findings using custom filters and mini-charts. The ultimate goal is to provide comprehensive insight into repeatable market behavior for strategic trading decisions.

3. Input Data Specifications:

3.1. Data Source and Manual Preparation Process:

The user will manually export historical OHLC data from MT5.

• MT5 Export Process:

  1. In MT5, go to the top menu: View -> Symbols .

  2. Select the desired symbol (e.g., EURUSD).

  3. Go to the Bars tab.

  4. Choose the appropriate timeframe, start date, and end date.

  5. Click Request to load data, then Export Bars to save as a text file.

• User's Excel Preparation:

  1. The automatically generated MT5 text file (tab-delimited) will be opened.

  2. The entire first column (containing all tab-separated data) will be copied.

  3. This copied column will then be pasted into the user's own custom Excel workbook.

  4. This process will be repeated for each required timeframe, resulting in multiple columns of raw MT5 data, placed side-by-side on the first sheet of the custom Excel file.

  5. Row 1 (Header Row): The user will manually insert a new first row.

     • Cell A1: Symbol Name (e.g., "EURUSD").

     • Above each column containing MT5 data, the user will manually write which timeframe the given column concerns, e.g., "1 minute".

• Data Volume: The Excel sheet is expected to contain a large volume of data, potentially over 1,000,000 rows per column, depending on data availability and Excel's capabilities.

3.2. Data Interpretation by Program:

The program must be capable of correctly parsing and interpreting the data from this custom Excel source. For each row (candle), it must correctly identify and process the following information:

• Symbol: Read from cell A1.

• Date Components: Year, Month, Day (as numbers).

• Day of Week: As words (e.g., "Monday", "Tuesday", etc.).

• Time Components: Hour, Minute, Second.

• Timeframe: Identified from the custom header in Row 1 for each data column.

• OHLC Values: Open, High, Low, Close of the given candle.

• Volume Data: Tick Volume, Volume (if available and exported correctly, otherwise Tick Volume).

• Spread: The spread value at the time of the candle.

4. Key Analytical Functions:

The program will perform comprehensive analysis based on the parsed data:

4.1. Pattern Identification and Directional Analysis:

• Timeframe Interval Search: Identification of consistent patterns within specified timeframe intervals (for example, for a 3-minute timeframe, find all occurrences of 09:03 to 09:06 for EVERY DAY).

• OHLC Differences & Trend Direction:

  • Calculation of the difference between various OHLC points (e.g., Close - Open for body, High - Low for total range).

  • Determination of the directional trend (Up/Down) of the candle based on Close vs. Open .

  • Calculation of profit/loss in pips/points for these differences.

  • Averaging these profit/loss values across all occurrences of the given pattern.

4.2. Wick/Shadow and Body Position Analysis:

• Lower Body Boundary vs. Low: Calculation of the difference between the lower boundary of the candle body (which can be Open or Close , whichever is lower) and Low .

• Upper Body Boundary vs. High: Calculation of the difference between the upper boundary of the candle body (which can be Open or Close , whichever is higher) and High .

• Body Position within Total Range (%):

  • Upper Body Position: Calculation of the percentage of the candle's total range ( High - Low ) that the upper body boundary (highest of Open , Close ) represents from the Low . (e.g., if the total range is 100 points and the upper body boundary is at 86 points from Low , the value here will be 86, and if the upper boundary is at the top, it's 100).

  • Lower Body Position: Calculation of the percentage of the candle's total range ( High - Low ) that the lower body boundary (lowest of Open , Close ) represents from the Low . (e.g., if the total range is 100 points and the lower body boundary is at 21 points from Low , the value here will be 21, and if the lower boundary is at the bottom, it's 0).

  • Averaging these percentage values.

4.3. Preceding and Subsequent Trend Analysis (Crucial):

The program must analyze trend continuation/reversal before and after the currently analyzed candle/interval, exclusively using the 1-minute timeframe data (from the first sheet). The currently analyzed candle/interval itself is NOT included in these trend calculations!

• a) "Clean" Trend: Defined as an uninterrupted sequence of candles (before or after the current candle) moving in the same direction without any opposing candle.

  • aa) Number of Candles: Count of consecutive candles in the same direction.

  • ab) Trend Length in Pips/Points: Total price movement from the Open of the first candle in the trend to the Close of the last candle in the trend.

• b) "Dirty" Trend: Defined as a sequence of candles in the predominant trend direction, which tolerates the presence of one or more counter-trend candles, provided these counter-trend candles do not disrupt the overall structure of the original trend.

  • Conditions for Acceptable Interruption (for an up trend): A counter-trend (downward) candle is acceptable if its Low (or any of its values) did NOT fall below the Low of the preceding candle (meaning the last one before the counter-trend candle that was in line with the trend) and after this counter-trend candle, the trend continued with a new High or Close above the High or Close before the counter-trend candle.

  • Conditions for Acceptable Interruption (for a down trend): A counter-trend (upward) candle is acceptable if its High (or any of its values) did NOT rise above the High of the preceding candle (meaning the last one before the counter-trend candle that was in line with the trend) and after this counter-trend candle, the trend continued with a new Low or Close below the Low or Close before the counter-trend candle.

  • ba) Number of Candles: Total count of candles in the "dirty" trend (excluding the currently displayed candle), including acceptable counter-trend candles.

  • bb) Trend Length in Pips/Points: Total price movement from the Open of the first candle of the "dirty" trend (or the lowest/highest point after an acceptable interruption) to the Close of the last candle of the trend.

5. Output and Display (Extended Requirements):

5.1. Tabular Display of Results:

The main output will be on a new sheet in Excel (e.g., "Results"), where each row represents an identified pattern. Columns will be in the following order and with defined filters:

• Symbol: (read from header)

• Time Segment (Based on Input Data):

  • Timeframe Interval: (e.g., M3)

  • Timeframe From When To When: Specific time interval of the identified pattern (e.g., 09:03:00 - 09:06:00).

• My Own Filter: Intraday Time Interval:

  • Will allow manual entry of a specific time (Year, Month, Day, Hour, Minute, Second) for both the start and end time of the analyzed interval. The program will then use the OHLC values from the 1-minute timeframe on the first sheet that fall within this manually defined segment.

  • Example: User enters 09:32:00 as start and 10:15:00 as end.

• Current Candle Trend / Manually Selected Intraday Time Interval:

  • Trend Direction: "Up" or "Down".

    • My Own Filter: Option for manual selection of "Up" or "Down" to filter results.

  • Trend Occurred Between ?? and ??: This will state the OHLC combination that determined the direction (e.g., "Open and Close", or "Low and High" for total range).

    • My Own Filter: Option for manual selection of any combination from "Open, High, Low, Close" to define which points should be compared for trend determination.

• Percentage of Same Trend Occurrence: Value 0-100%.

  • My Own Filter: Option for manual entry of "is greater than" and a number (0-100), e.g., "90", to display only patterns that occurred 90% or more of the time.

  • Visualization of Incorrect Occurrences: A checkbox that, when activated, will highlight in red cells (or entire rows) in the first source Excel sheet (in the given column/timeframe) where the pattern did NOT repeat as expected. This will allow the user to then filter by color for detailed manual analysis.

• Average Difference in Pips/Points:

  • a) For "Candle Body": Difference between Open and Close .

    • My Own Filter: "is greater than", value (0+ pips).

  • b) For "Entire Candle including Wicks/Shadows": Difference between Low and High .

    • My Own Filter: "is greater than", value (0+ pips).

  • c1) Difference Between Lower Body Boundary and Low: Average.

    • My Own Filter: "is greater than", value (0+ pips).

  • c2) Position of Upper Body Boundary Relative to Total Candle Range (in % from Low): Average (0-100%).

    • My Own Filter: "is greater than", value (0+).

  • d1) Difference Between Upper Body Boundary and High: Average.

    • My Own Filter: "is greater than", value (0+ pips).

  • d2) Position of Lower Body Boundary Relative to Total Candle Range (in % from Low): Average (0-100%).

    • My Own Filter: "is greater than", value (0+).

• Average (Tick) Volume:

  • My Own Filter: "is greater than", value (0+).

• Average Spread:

  • My Own Filter: "is greater than", value (0+).

• Previous Trend (Excluding Currently Displayed Candle):

  • a) Clean Trend:

    • aa) Number of Candles:

      • My Own Filter: "is greater than", value (e.g., "2").

    • ab) Trend Length in Pips/Points:

      • My Own Filter: "is greater than", value.

  • b) Dirty Trend:

    • ba) Number of Candles:

      • My Own Filter: "is greater than", value (e.g., "2").

    • bb) Trend Length in Pips/Points:

      • My Own Filter: "is greater than", value.

• Future Following Trend (Excluding Currently Displayed Candle): (Same columns and filters as for Previous Trend).

5.2. Graphical Display (Mini-charts):

Upon selecting a row with a specific pattern (e.g., by clicking or checking a box), a set of mini-charts (several side-by-side) for this specific pattern will be displayed at the bottom of the screen/Excel (with automatic scrolling/maintaining position at the bottom when scrolling with the right cursor/bar):

1. Monthly Occurrences Graph:

   • Horizontal axis: Months of the year (textually: "January", "February", ...).

   • Right vertical axis: Percentage of repetition from 0 to 100%.

   • Each bar above a given month will be divided into red color and a number (for non-repeating occurrences) and green color and a number (for repeating occurrences).

2. Weekly Occurrences Graph:

   • Horizontal axis: Days of the week (textually: "Monday", "Tuesday", ...).

   • Right vertical axis: Percentage of repetition from 0 to 100%.

   • Same color distinction as the monthly graph.

3. Trend Direction Graph: Visualization of average profit/loss for "Up" and "Down" directions of the analyzed pattern.

4. OHLC Differences Graph: Visualization of average values for a), b), c1), c2), d1), d2) from section 5.1 (Average Difference in Pips/Points).

5. Volume Graph: Average tick volume for the pattern.

6. Spread Graph: Average spread for the pattern.

7. Previous Clean Trend Graph: Visualization of average number of candles and length in pips for the "clean" trend BEFORE the pattern.

8. Previous Dirty Trend Graph: Visualization of average number of candles and length in pips for the "dirty" trend BEFORE the pattern.

9. Future Clean Trend Graph: Visualization of average number of candles and length in pips for the "clean" trend AFTER the pattern.

10. Future Dirty Trend Graph: Visualization of average number of candles and length in pips for the "dirty" trend AFTER the pattern.

6. Technical Requirements:

• Platform:

  • Preferred: Microsoft Excel Add-in (VBA) for seamless integration with the user's workflow.

  • Alternative: Standalone Python script with a simple GUI that can read/write to Excel files.

• Language: VBA or Python.

• User Friendliness: The tool must be designed for intuitive use even by non-technical users.

7. Important Note on Automatic Data Download:

If the developer is able to ensure that the program will be capable of automatically downloading data directly from MT5 (for all pre-selected timeframes and assets), this is acceptable and welcome. However, given the critical nature of the input data, this automatic download must be absolutely flawless and reliable! If there is any risk of errors during automatic download, the original manual export and copying process is preferred to ensure data integrity.

8. Budget:

The price for this project is negotiable, with a maximum budget of 30 USD.