Gann Square Of Nine: Excel Template And Library

By Lawrence

This is a brief documentation for the free Gann Square of Nine Excel Workbook file located in the software store.

I have some material written on the subject of Gann Square that I planned to publish soon. Then I realized that without a working Gann Square on hand it will be difficult for many people to understand what I am talking about. It is better to upload the Excel workbook and the code library first and then follow up with the articles.

Basic Usage of the Gann Square of Nine Excel Workbook

First it is an Excel 2007 file so you need a newer version of Excel to work with it. You also need the VBA enabled in the workbook to use it as the Gann Square functions are implemented in Excel VBA. And before anyone asking why not do it in Google spreadsheet, it is not a good idea at all because Excel is way better in this aspect in terms of customization and application building.

After opening the workbook, the example gann square will show up. You can use it as is. Print it out for reference, look up values from there, etc.

Gann Square Of Nine from DaytradingBias.com

There is also a second spreadsheet in there called Gann Square Custom. It showcases the possibility of constructing custom gann sqaure based on different initial settings.

Customized Gann Square Of Nine from DaytradingBias.com

Customization And Utilization Of The Gann Square of Nine Excel VBA Library

When you want to go one step further and build your own customized Gann Square, then you need to follow the following steps.

1. Make a copy of the workbook using a different name as you need the VBA code library from the workbook. Advanced Excel users can do it in one of many other ways in including the code library.

2. On the spreadsheet that you want to create a new Gann Square, locate the cell that you want to put the Gann Square to and give it a name using Name A Range command in Excel. The example spreadsheets named the center cells as gann and gannc.

3. Fill every cell in the region for Gann Square with this single formula,

=gannValue (center cell name)

3a. if you want to experiment with custom Gann Square configuration you can use the following formula instead,

=gannValueCustom (center cell name, center square value, increment value)

4. Ring color formatting in the examples are done using conditional formatting in Excel. The formula is essentially a reverse calculation of the ring value based on the property of Gann Square which I will discuss in a separate article.

Following is the conditional formatting formula used in the custom gann square example,

=MOD(ROUNDUP((SQRT((RC-3)/0.5+1)-1)/2,0),2)=0

"RC" in the formula is the current cell relative reference in R1C1 style.

"3" in the formula is the centre cell value.

"0.5" is the increment value for each step.

Purpose Of The Workbook

It is a useful building block for anyone interested in constructing Gann Square but do not know how it can be done without hard coding formulas in a spreadsheet.

Anyone interested in additional functions added to the library please let me know.

Chart Reading Skill Matters

Gann square is not a tool that can work by itself. You need proper training in classic chart reading to be able to fully utilize the information you get from Gann Square. To upgrade your chart reading skill, I suggest you start with my book Art of Chart Reading available at Amazon.

Share

Comments
  • iactnow April 28, 2014 at 3:21 am

    Hello, I like the way you implmented the gann square in excel. Just wondering are you still adding more methods to your Gann square if so I have a few suggestions

    • Lawrence Chan April 28, 2014 at 8:31 pm

      Just post what you like to see added.

      It is an open source template. I was hoping for more people to join the effort in improving it but after thousands of download so far, no one expressed any interest in doing so …

      I am not actively developing the template but some members here may help out with what you want. So post away and hope for the best!

  • iactnow April 29, 2014 at 1:41 am

    Hi Lawrence,
    All other excel square sheets I’ve seen seem to tediously count around to get the values whereas yours uses the square function/property to calculate values.

    Things I’m interested in are:
    1. been able to switch between a value, date or time
    2. Selecting a value on the square chart and the chart automatically highlights the corresponding values at 45, 90, 135, etc
    3. Entering a value and a date and having the program hi-light the corresponding cell on the sheet plus the 45, 90, 135 values plus calculating the corresponding dates for each of the hi-lighted values, and also listing the values and dates.

    Thats a few of the ideas I’m interested in.

    • Lawrence Chan April 30, 2014 at 10:45 pm

      First, it is best to post the correspondence in the forum because it is where the excel template is downloaded from.

      #1 you need to provide examples of what you are looking for. pictures would be best.

      #2 & #3 what you are looking for is beyond what a basic Excel template can do. it is closer to an Excel application.

  • KUMA_OUSHI February 14, 2015 at 10:07 pm

    VERY WELL WRITTEN AND EASY TO ARTICLE TO UNDERSTAND. MY QUESTION IS HOW MAY i RECEIVE A COPY OF THE GANN EXCEL SPREADSHEET

    • Lawrence February 15, 2015 at 6:05 am

      Follow the link in the article will take you to the user forum download area. =)

  • rkayz November 24, 2017 at 4:41 am

    How do we use it for trading?

  • volpiemanuele March 4, 2019 at 7:52 am

    How do you find the file excel of Gann t9 square ? Thanks

  • beto April 14, 2019 at 9:58 am

    Hello mate, hope all is good. Can you add one more function to the excel file? I am still learning and don’t know how to edit and add formulas. I have found one youtube video with the formula to find intraday reversal time using the square of nine, formula is =MOD((SQRT(Price)*180)-225,360) and let’s say opening price is 9801, then =MOD((SQRT(9801)*180)-225,360) which equals 315 degrees.
    So we convert degrees to time by dividing 315 by 60 minutes which is 5.3 (5 hours and 18minutes). So we add this to the opening price of the day, let’s say
    09:00AM then we get 14:18PM. This is the probable reversal time. But sometimes price keeps going, so it is good to have some extra time
    projections, so we need to add more degrees to the resultlike, 30, 60, 90. Can you implement this in the excel file? Thanks!

  • Rajendra October 30, 2021 at 12:57 am

    Hi Lawrence, Thanks for this. I wanted to build gann squares for values upto 100000 but don’t want to spend time to reinvent the wheel, thus stumbled upon your article while searching on the net and found this is a perfect fit for my purpose. Can I use your file? Thanks in advance.

  • You must be logged in to comment. Log in