title: Fastest approach to read and process 10k Excell cells in Python/Pandas?
body: <p>I want to read and process realtime DDE data from a trading platform, using Excel as 'bridge' between trading platform (which sends out datas) and Python which process it, and print it back to Excel as front-end 'gui'. SPEED IS CRUCIAL. I need to:</p>
  
  <ul>
  <li><p>read 6/10 thousands cells in Excel as fast as possible</p></li>
  <li><p>sum ticks passed at same time (same h:m:sec) </p></li>
  <li><p>check if DataFrame contains any value in a static array (eg. large quantities) </p></li>
  <li><p>write output on the same excel file (different sheet), used as front-end output 'gui'. </p></li>
  </ul>
  
  <p>I imported '<strong>xlwings</strong>' library and use it to read data from one sheet, calculate needed values in python and then print out results in another sheet of the same file. I want to have Excel open and visible so to function as 'output dashboard'. <strong>This function is run in an infinite loop</strong> reading realtime stock prices.</p>
  
  <pre><code>import xlwings as xw
  import numpy as np
  import pandas as pd
  
  ...
  ...
  
  tickdf = pd.DataFrame(xw.Book('datafile.xlsx').sheets['raw_data'].range((1,5)(1500, 8)).value)
  tickdf.columns = ['time', 'price', 'all-tick','symb']
  tickdf = tickdf[['time','symb', 'price', 'all-tick']]
  #read data and fill a pandas.df with values, then re-order columns
  
  try:
     global ttt #this is used as temporary global pandas.df
     global tttout #this is used as output global pandas.df copy
     #they are global as they can be zeroed with another function
  
     ttt= ttt.append(tickdf, ignore_index=False) 
     #at each loop, newly read ticks are added as rows to the end of ttt global.df.
  
     ttt.drop_duplicates(inplace=True)
  
     tttout = ttt.copy()
     #to prevent outputting incomplete data,for extra-safety, I use a copy of the ttt as DF to be printed out on excel file. I find this as an extra-safety step
  
     tttout = tttout.groupby(['time','symb'], as_index=False).agg({'all-tick':'sum', 'price':'first'})
     tttout = tttout.set_index('time')
     #sort it by time/name and set time as index
  
     tttout = tttout.loc[tttout['all-tick'].isin(target_ticker)] 
     #find matching values comparing an array of a dozen values
  
     tttout = tttout.sort_values(by = ['time', 'symb'], ascending = [False, True])
     xw.Book(file_path).sheets['OUTPUT'].range('B2').value = tttout
  </code></pre>
  
  <p>I run this on a i5@4.2ghz, and this function, together with some other small other code, runs in 500-600ms per loop, which is fairly good (but not fantastic!) - I would like to know if there is a better approach and which step(s) might be bottlenecks.</p>
  
  <p>Code reads 1500 rows, one per listed stock in alphabetical order, each of it is the 'last tick' passed on the market for that specific stock and it looks like this:</p>
  
  <pre><code>'10:00:04 | ABC | 10.33 | 50000'
  '09:45:20 | XYZ | 5.260 | 200 '
  '....
  </code></pre>
  
  <p>being time, stock symbol, price, quantity.</p>
  
  <p>I want to investigate if there are some specific quantities that are traded on the market, such as 1.000.000 (as it represent a huge order) , or maybe just '1' as often is used as market 'heartbeat', a sort of fake order.</p>
  
  <p>My approach is to use Pandas/Xlwings/ and 'isin' method. Is there a more efficient approach that might improve my script performance? </p>