Using Python to extract CoT reports to trade Futures/FX

Frank Rithesh Pereira Mar 25 2021 · 3 min read
Share this
(Pic by


·          Frank R Pereira

·          Melroy E Pereira

The article will give you a step by step guide on how to extract commitment of trader’s reports using Python and then how to export the information into Power BI and analyse positions taken by Hedge funds/Big Banks.

System Requirements:

·          Python IDE (Any - Jupyter, Spyder or PyCharm)

·          Power BI

·          Excel

Before we begin, those who are new in the space of financial trading, let me give you a brief introduction on what CoT reports are?

Cot Reports are published weekly showing aggregate positions/holdings in different asset classes of market participants. These reports are published every Friday and these reports can you give you an indication which way the futures or FX markets are heading towards.

Now, let’s see how we can extract the information using Python and visualize it using Power BI. Please note that the code is written here can be written in multiple different ways and its certainly not the only way. Also, only snippets of the code is given here and the full code can be found on my GitHub account.

Before we begin, the data need to be copied and pasted into an excel sheet and then save the file in a specific location. Once it’s done you can use python to extract the data from a specific file.

Importing libraries:

Creating a function to scrape the data from different rows:

Printing strings extracted from the report:

Converting the strings into a data frame:

Printing the output:

Printing the all asset classes which are being converted into data frames:

Renaming columns:

Splitting the data into multiple columns:

Printing the output:

Replacing whitespaces into np.nan values:

Printing the output:

Dropping nan columns:

Renaming columns:

Appending all datasets together:

Renaming row names and filling nan values with 0

Once the function is written download the file as .py and then save it in a specific location and call the function as a library.

The final output:

Once the output file is saved in an excel format you can populate custom visuals using Power BI. I will be writing a separate article on how to create visuals using Power BI in my next article.

If you liked our articles then please do endorse us on LinkedIn.

 Frank Pereira , Melroy Pereira

Code for the above is given in our GitHub

Frank Pereira , Melroy Pereira         

Some of the Python/Power BI resources are;

  • Andrew Ng Coursera classes    
  • Krish Naik and Sudhanshu Kumar   
  • Thank you for reading

    Read next