Python searches a variety of directories for its libraries, something I had largely lived in peace with until I had to modify some legacy code to address a suspected timing bug.
The overall task is to produce a set of charts for a customer. Python loads an Excel workbook, a time-series data set is updated from a third-party source, and a Visual Basic Excel macro iterates through the workbook’s numerous tabs, adjusts to fiscal quarters, and emits several dozen charts. It’s clunky, but what I was given to work with. Until recently, was functioning well-enough.
from win32com.client import Dispatch def run_macro(filename, macro): excel = Dispatch('Excel.Application') excel.Visible = False excel.DisplayAlerts = False wb = excel.Workbooks.Open(filename) try: excel.Run(macro) wb.Close(True) except Exception as err: wb.Close(False) raise err finally: excel.Quit()
The third-party data source is a time-series of currency pairs. I would much prefer to work at the API level, and not request unchanging history each week, but my list of things to streamline is now infinity + 1.
=RHistory("JPY=",".Timestamp;.Open;.High;.Low;.Close","START:2017-01-01 TIMEZONE:TOK END: 2019-05-31 INTERVAL:1D",,"SORT:ASC TSREPEAT:NO CH:Fd",B2)
When the service authenticates (which is rather slow), data is cleared in the worksheet and repopulated. I think what’s happening is it takes long enough that when the macro kicks in, data is still being moved around. As a workaround, I added a sixty second delay before the macro is called to give things a time to settle down.
Then I ran into a problem, my change didn’t appear to be “taking.” The python environment was getting the function from a completely different place. The best way I found to determine where was this snippet:
import imp imp.find_module("some_library")