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

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 tool to help analyze historical data from MT5 and automatically search for recurring price patterns. The primary goal is to provide clear statistics on the profitability and success rate of these patterns, including detailed analysis of candles, wicks, and the relationship with trends that preceded and followed the pattern.

Despite the detailed specification, I am fully open to discussion and prepared to talk through any simplifications or optimizations that could save time, reduce costs, and minimize errors. The most important thing for me is a functional result that reliably helps with decision-making.

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, and end date.

  5. Click Request to load data, then Export Bars to save as an Excel file.

• User's Excel Preparation:

  1. The automatically generated Excel file from MT5 will be opened.

  2. The entire first column (containing all 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 the corresponding timeframe (e.g., "1 minute", "5 minutes", "1 hour").

• 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 text (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, Real 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 (the higher 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 is 86%).

  • Lower Body Position: Calculation of the percentage of the candle's total range (High - Low) that the lower body boundary (the lower 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 is 21%).

  • Averaging these percentage values.

4.3. Preceding and Subsequent Trend Analysis (Crucial):

This section aims to analyze the price trend that preceded and followed the identified price pattern. The analysis will be performed exclusively on data from the 1-minute timeframe. The currently analyzed candle/interval (i.e., the pattern itself) is NOT included in the trend calculation.

The goal is to categorize historical trends into two types—"clean" and "dirty"—and calculate their parameters (number of candles, length in pips/points) for three defined phases:

1. Trend before the pattern: Analysis of the trend that immediately precedes the currently observed pattern.

2. Trend within the pattern: Analysis of the pattern itself (already described above in section 4.1).

3. Trend after the pattern: Analysis of the trend that immediately follows the currently observed pattern.

• Definitions of Trend Types:

  • a) "Clean" Trend (Clean Trend):

    • This is an uninterrupted sequence of candles moving in the same direction.

    • Calculation: The total number of candles in this uninterrupted sequence and the trend length in pips/points are calculated from the Open of the first candle to the Close of the last candle in the given trend segment (before or after the current pattern).

  • b) "Dirty" Trend (Dirty Trend):

    • This is a sequence of candles moving in one predominant direction, but it tolerates an interruption by one or more counter-trend candles. The interruption must not disrupt the overall structure of the original trend.

    • Specification of Counter-Trend Candles:

      • For an Up Trend: A counter-trend candle (with Close below Open) is acceptable if its High or Low (or any other value) does not fall below the Low of the preceding candle in the trend. The trend is considered to be continuing if, after the counter-trend candle, a candle follows that creates a new High or Close above the High or Close of the preceding candle in the trend.

      • For a Down Trend: A counter-trend candle (with Close above Open) is acceptable if its High or Low (or any other value) does not rise above the High of the preceding candle in the trend. The trend is considered to be continuing if, after the counter-trend candle, a candle follows that creates a new Low or Close below the Low or Close of the preceding candle in the trend.

    • Calculation: The total number of candles in this sequence, including acceptable counter-trend candles, and the trend length in pips/points are calculated from the Open of the first candle to the Close of the last candle in the given trend segment (before or after the current pattern).

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 a found pattern. The 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 found pattern (e.g., 09:03:00 - 09:06:00).

• My Custom Filter: Intraday Time Interval:

  • Allows 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 that fall within this manually defined segment.

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

• Current Candle Trend / Manually Selected Intraday Time Interval:

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

    • My Custom 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 the total range).

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

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

  • My Custom Filter: Option for manual entry of "is greater than" and a value (0-100), e.g., "90", to display only patterns with a high success rate.

  • Automatic Sum of All Analyzed Data: The program will automatically display the total number of analyzed data points here (e.g., "123"). This value will indicate how many candles/intervals the percentage is calculated from. Example: "90% success rate from a total of 123 analyzed samples".

  • Visualization of Incorrect Occurrences: A checkbox that, when activated, will highlight in red the 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 subsequently filter by color for detailed manual analysis.

• Average Difference in Pips/Points:

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

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

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

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

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

    • My Custom 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 Custom Filter: "is greater than", value (0+).

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

    • My Custom 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 Custom Filter: "is greater than", value (0+).

• Average (Tick) Volume:

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

• Average Spread:

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

• Previous Trend (Excluding Currently Displayed Candle):

  • a) Clean Trend:

    • aa) Number of Candles:

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

    • ab) Trend Length in Pips/Points:

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

  • b) Dirty Trend:

    • ba) Number of Candles:

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

    • bb) Trend Length in Pips/Points:

      • My Custom 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):

Regarding the visual display of details for a selected row/pattern, I'm essentially flexible about the form you choose. It could be dynamic mini-charts, a display on a new sheet using static tables, or any other method. For me, the key is to have a quick and clear visual overview of all relevant details after selecting a row, in a way that is most technically efficient for you.

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:

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

   • Vertical axis: Percentage of repetition (0-100%).

   • Each bar for a month will be divided into red color (with a number) for cases where the pattern did NOT repeat as expected, and green color (with a number) for cases where the pattern did repeat.

2. Weekly Occurrences:

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

   • Vertical axis: Percentage of repetition (0-100%).

   • Same color distinction as the monthly graph.

3. Trend Direction Graph: Visualization of average profit/loss for the "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 the average number of candles and length in pips for the "clean" trend BEFORE the pattern.

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

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

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

6. Technical Requirements:

• Platform:

  • Preferred: Standalone Python script with a simple GUI (Graphical User Interface) that can read/write to Excel files. Python offers a robust and flexible solution for processing large volumes of data.

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

  • The final decision and proposal on which solution will be most suitable is left to the developer, as the most important thing for me is a reliable and functional result.

• Language: Python or VBA.

• 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. I would like to add that, although the specification is detailed, I am fully open to discussion. If the project seems complex at first glance, or if you have any suggestions for improvements or simplifications to save time, reduce costs, and minimize process errors, I am ready to discuss them and be flexible.