This first article will cover requesting live market data through the API, so please be familiar with the code from our Python API Market Data tutorial<\/a> before entering into this Excel series. <\/p>\n\n\n\n Let\u2019s start by taking note of the packages we will be using throughout the article series. For this first article, we will be implementing the Interactive Brokers ibapi<\/strong> library, along with queue<\/strong>, threading<\/strong>, time<\/strong>, and xlwings<\/strong>. Users are welcome to explore more of xlwings on their own by reviewing the xlwings API reference<\/a>. We will also be separating our workflow into multiple files for readability, so please be aware that each file will be importing several of the files created throughout this lesson.<\/p>\n\n\n\n Let\u2019s start with creating a new Python file, utils.py. In our new file, we can add all of our imports relevant for this section of the tutorial. let\u2019s add the import for xlwings using On a new line, let\u2019s create our workbook name as a global variable \u2013 I will use With our variables set, let\u2019s create a method to create a workbook if one does not exist. This is an optional step as users can create the workbook directly; however, this will help establish a baseline file for us to work in. To begin, we\u2019ll create a new method, createBook(), that we can use to build a fresh workbook if it doesn\u2019t exist. On a new line, let\u2019s create a variable, \u2018bk\u2019, set to a book variable, I will instead opt to modify our sheet to avoid some relative references. So next, I will add Going back to the root indent level, we\u2019ll build a simple try loop to reference our file if it exists, or create the workbook if it doesn\u2019t.<\/p>\n\n\n Now if we move to run our Python file, we should see the workbook created in the current directory, and the first sheet labeled as \u201cLive_Data\u201d. Closing the workbook and returning to our utils.py file, we can get to work on some of the more interesting methods. We\u2019re going to be building out some headers for our sheet; however, this will require a few prerequisite methods before we can get started.<\/p>\n\n\n\n We\u2019ll start with a method to shift to the appropriate column as needed. Because Interactive Brokers tick types are centered around integer values that can be enumerated, it is best to find ways of converting an integer value to an associated column value. We\u2019ll manage this by a new letterIncr() method that takes an integer value, letter_int, as an argument and converts it to a character using the Python chr() and ord() methods. We\u2019ll assign the variable, incr_letter, to the ordered character value of an integer. It\u2019s important to increment our standard value by 2 because this will resolve Python\u2019s 0-value indexing with Excel\u2019s 1-value index, and we will skip the first column, A, as we can use that for our Symbol column manually. After calculating the column character, we can return our incr_letter variable. The resulting method should reflect the example below:<\/p>\n\n\n\n With our column assignment structure in place, let\u2019s focus on writing content into our workbook. Before writing however, we want to implement the Queue class to facilitate multithreading in our program. This will allow us to write to multiple cells simultaneously instead of updating one value at a time. This concept of concurrency and asynchronicity is further discussed in our Understanding Asynchronous Libraries with TWS API<\/a> article.<\/p>\n\n\n\n To implement threading for our workbook, we\u2019ll start by assigning the variable, q, to the Queue() class object. Now, we can create an iterator for however many threads we need. I will set my range to 50 working threads, though users may want to tweak these values depending on how many symbols they would like to handle in tandem with much their computer can withstand. Then we\u2019ll assign a new variable, t, to a thread which targets a writeToWorkbook() method along with setting the thread as a daemon. We will create the full writeToWorkbook() method momentarily. Finally, we\u2019ll start our thread, and on the root indent level, we\u2019ll call the join() method for q. The resulting code is displayed below:<\/p>\n\n\n\n Lets go ahead and create our writeToWorkbook() method. We\u2019ll begin by creating a while loop to continuously write to the sheet, as our queue will constantly be feeding data from Interactive Brokers in just a moment. Within the loop, we\u2019ll assign the variable, params, to q.get(). This will essentially capture all content sent to the queue. We\u2019ll then split params into three separate variables, book, cell, and content, so we can manipulate our request as we move along. Given we\u2019ve only received a list, we\u2019ll capture the arguments with index positions 0, 1, and 2 respectively. Finally, we\u2019ll reference xw.Book(BN) as our active Workbook, then declare the exact sheet with our book and cell within it\u2019s range to set the value. We\u2019re essentially accessing a nested reference of the workbook through xlwings\u2019 architecture. Clients may also optionally print the params variable so they can verify the sheet\u2019s values against the Python feed directly to observe latency issues. A final product for the writeToWorkbook() method is shown below:<\/p>\n\n\n\n Finally, much of our foundation is completed, and we can start working on displaying data within Excel. We can develop a simple buildHeaders() method to create our column headers. Reflecting on the work we\u2019ve done, let\u2019s put the Queue to work using While we will build this out further in a moment, we can test the current behavior to make sure cell A1 is assigning the Symbol value appropriately. At the bottom of the page, let\u2019s set the name-main idiom to our new buildHeaders() method, as shown below:<\/p>\n\n\n\n If we save and run our file, after a moment, we should see our Python_Excel.xlsx file launch, and show \u201cSymbol\u201d in the first column of our Live_Data sheet.<\/p>\n\n\n\nCreating utils.py<\/h4>\n\n\n\n
import xlwings as xw<\/code>. We will also be using the Queue object from the queue library, the Thread object from threading, and sleep from the time library. Users are also welcome to import the full library as they see fit, though for readability and runtime processing, we will only import the necessary components of these libraries. <\/p>\n\n\n\nimport xlwings as xw\nfrom threading import Thread\nfrom queue import Queue\nfrom time import sleep\nfrom ibapi.ticktype import TickType, TickTypeEnum\nfrom ibapi.client import *\nfrom ibapi.wrapper import *<\/pre>\n\n\n\n
BN = \u201cPython_Excel.xlsx\u201d<\/code>. This will save the file to the same directory as the Python file; however, you can set an absolute path instead like 'C:\\\\Users\\\\awise\\\\Desktop\\\\Python_Excel.xlsx'<\/code> to save the excel sheet to my desktop. I will also create a worksheet variable, LD = \u201cLive_Data\u201d<\/code>. This should result in the following code:<\/p>\n\n\n\n BN = \u201cPython_Excel.xlsx\u201d\nLD = \u201cLive_Data\u201d<\/pre>\n\n\n\n
bk = xw.Book()<\/code>. Next, we\u2019ll save the workbook with bk.save(BN)<\/code>. And we could end the method using bk.activate(BN)<\/code> though that will only establish our sheet as a current sheet.<\/p>\n\n\n\nbk.sheets.add(name=LD)<\/code>. This will add a new sheet to our workbook based on our prior LD variable. Optionally, you can then delete the default sheet, Sheet1, using bk.sheets(\u2018Sheets\u2019).delete()<\/code>. Now we can actually end our method by saving our workbook by calling bk.save(BN) once again<\/code>. Our complete createBook() method should look like:<\/p>\n\n\n\ndef createBook():\n bk = xw.Book()\n bk.save(BN)\n bk.activate(BN)\n bk.sheets.add(name=LD)\n bk.sheets('Sheet1').delete()\n bk.save(BN)<\/pre>\n\n\n\ntry:\n xw.Book(BN)\nexcept FileNotFoundError:\n createBook()<\/pre>\n\n\n
def letterIncr(letter_int):\n incr_letter = chr(ord('@')+letter_int+2)\n return incr_letter<\/pre>\n\n\n\nq = Queue()\n\nfor i in range(50):\n t = Thread(\n target=writeToWorkbook, \n daemon=True\n ).start()\nq.join()<\/pre>\n\n\n\n
def writeToWorkbook():\n while True:\n params = q.get()\n book = params[0]\n cell = params[1]\n content = params[2]\n xw.Book(BN).sheets[book].range(cell).value = content\n print(params)<\/pre>\n\n\n\n
q.put([LD, \u2018A1\u2019, \u2018Symbol\u2019])<\/code>. This will make cell A1 equal to the value, \u201cSymbol\u201d. We\u2019ll use this column to display which contracts we are working with. The method should currently appear as:<\/p>\n\n\n\ndef buildHeaders():\n q.put([LD,'A1',\"Symbol\"])<\/pre>\n\n\n\n
if __name__ == \"__main__\":\n buildHeaders()<\/pre>\n\n\n\n
![\"Python](\"\/campus\/wp-content\/uploads\/sites\/2\/2024\/02\/pexcel_symbol.png\")
<\/p>\n\n\n\nCompleted utils.py<\/h6>\n\n\n\n
import xlwings as xw\nfrom threading import Thread\nfrom queue import Queue\nfrom time import sleep\nfrom ibapi.ticktype import TickType, TickTypeEnum\nfrom ibapi.client import *\nfrom ibapi.wrapper import *\n\nBN = 'Python_Excel.xlsx'\nLD = 'Live_Data'\n\ndef createBook():\n bk = xw.Book()\n bk.save(BN)\n bk.activate(BN)\n bk.sheets.add(name=LD)\n bk.sheets('Sheet1').delete()\n bk.save(BN)\n\ndef buildHeaders():\n q.put([LD,'A1',\"Symbol\"])\n for i in range(9):\n x = letterIncr(i)\n q.put([LD,'{}1'.format(x),TickTypeEnum.toStr(i)])\n\ndef letterIncr(letter_int):\n incr_letter = chr(ord('@')+letter_int+2)\n return incr_letter\n\ndef writeToWorkbook():\n while True:\n params = q.get()\n book = params[0]\n cell = params[1]\n content = params[2]\n xw.Book(BN).sheets[book].range(cell).value = content\n print(params)\n\nq = Queue()\n\nfor i in range(50):\n t = Thread(\n target=writeToWorkbook, \n daemon=True\n ).start()\nq.join()\n\n\ntry:\n xw.Book(BN)\nexcept FileNotFoundError:\n createBook()\n\nif __name__ == \"__main__\":\n buildHeaders()<\/pre>\n\n\n\nCreating LiveData.py<\/h4>\n\n\n\n
![\"Excel](\"\/campus\/wp-content\/uploads\/sites\/2\/2024\/02\/excel_live_updates-700x429.gif\")
\n\n\n\n