我自己的解决方案是让Python使用pymongo和win32com将它们粘合在一起。然后你就可以随心所欲了。在我的例子中,Python循环只是不断地“监听”某些Excel单元格,从Mongo调用它需要的内容,然后将其放回Excel中。它很灵活,很多事情都可以这样做。这是完整的代码库,但是您必须更改对Mongodb的调用,以匹配您自己的数据库。在这里,您还将看到一些可以从Python中更改Excel单元格的颜色和内容的方法。哦,我应该提到的是,它充满了ansi转义序列,所以您可能希望从中运行Python
ansicon
或
ConEmu
.
import win32com.client as win32
import time # will need this for time parsing
from optparse import OptionParser
import pdb # debugger, when necessary
import string # for string parsing and the alphabet
from pymongo import MongoClient
import inspect
from datetime import datetime, timedelta, tzinfo
from dateutil import tz
from bson.son import SON
import msvcrt # for getch
import os
import sys # for stdout.write
from collections import OrderedDict
def parseCmdLine():
parser = OptionParser(description="Retrieve realtime data.")
parser.add_option("--f",
dest="file",
help="filename",
default="bbcapture.xls")
parser.add_option("--mongohost",
dest="mongohost",
default="192.168.1.30")
parser.add_option("--mongoport",
dest="mongoport",
type="int",
default=27017)
(options, args) = parser.parse_args()
return(options)
options = parseCmdLine() # parse the commandline
client = MongoClient(options.mongohost, options.mongoport) # link to mongo
db = client.bb # the database
bbsecs = db.bbsecs # now all the collections
bbdaily = db.bbdaily
bbticks = db.bbticks
linkstatusperiod = False # for the moving period in the top left excel cell showing we're linked
def ansi(colour = "white", bright = False, back = "black"):
# ansi colour sequences
brit = {True: "\033[1m",
False: "\033[0m"}
colo = {"black": "\033[30m",
"red": "\033[31m",
"green": "\033[32m",
"yellow": "\033[33m",
"blue": "\033[34m",
"magenta": "\033[35m",
"cyan": "\033[36m",
"white": "\033[37m"}
bakk = {"black": "\033[40m",
"red": "\033[41m",
"green": "\033[42m",
"yellow": "\033[43m",
"blue": "\033[44m",
"magenta": "\033[45m",
"cyan": "\033[46m",
"white": "\033[47m"}
sys.stdout.write(brit[bright])
sys.stdout.write(colo[colour])
sys.stdout.write(bakk[back])
def mdaily(ticker = "USDEUR Curncy", field = "LAST_PRICE", sortdirection = 1, numget = 1000000):
ansi("cyan", False)
print "\nGetting", ticker, "field", field, "from Mongo...",
lister = OrderedDict()
#for post in bbdaily.find({"ticker": ticker, "fieldname": field}).limit(numget).sort("time", sortdirection):
for post in bbdaily.find({"$query": {"ticker": ticker, "fieldname": field}, "$orderby": {"time": -1}}).limit(numget):
lister[str(post["time"])] = post["fieldvalue"]
ansi("cyan", True)
print "got", len(lister), "values",
ansi()
return lister
def mtick(tickers, sortdirection = 1, numget = 1000000):
if len(tickers) == 0:
return []
else:
ansi("green", False)
print "\n Getting minutes for for", tickers,
tickerdic = OrderedDict()
for eachticker in tickers:
eachdic = dict()
print numget
for post in bbticks.find({"ticker": eachticker}).limit(numget):
eachdic[post["time"]] = [post["open"], post["high"], post["low"], post["close"]]
ansi("green")
tickerdic[eachticker] = eachdic
print "got", len(eachdic), "for ticker", eachticker,
ansi("green", True)
print "got", len(tickerdic), "tickers",
dates = [set(tickerdic[x].keys()) for x in tickerdic] # get all the dates
dates = set.intersection(*dates) # get the unique ones
dates = [x for x in dates] # convert to list
if sortdirection == -1:
dates = sorted(dates, reverse = True)
else:
dates = sorted(dates, reverse = False)
retlist = [[[x, tickerdic[y][x][0], tickerdic[y][x][1], tickerdic[y][x][2], tickerdic[y][x][3]] for x in dates] for y in tickerdic.keys()]
ansi()
return retlist
def getsecs():
seclist = []
for post in bbsecs.find():
seclist.append(post["ticker"])
return(seclist)
def offsetString(startrow, startcol, endrow, endcol):
startrowstr = str(startrow)
endrowstr = str(endrow)
if(startcol > 26):
startcolstr = string.uppercase[startcol / 26 - 1] + string.uppercase[startcol % 26 - 1]
else:
startcolstr = string.uppercase[startcol - 1]
if(endcol > 26):
endcolstr = string.uppercase[endcol / 26 - 1] + string.uppercase[endcol % 26 - 1]
else:
endcolstr = string.uppercase[endcol - 1]
return(startcolstr + startrowstr + ":" + endcolstr + endrowstr)
def main():
excel = win32.gencache.EnsureDispatch("Excel.Application")
excel.Visible = 1
try: # try to link to the file
ansi("red", False)
print "Linking to", options.file
wb = excel.Workbooks(options.file)
ws = wb.Worksheets("MongoData")
ansi()
except: # not open then try to load it
try:
ansi("red", False)
print "Not open.... trying to open in current directory", os.getcwd()
ansi()
wb = excel.Workbooks.Open(os.getcwd() + "\\" + options.file)
ws = wb.Worksheets("MongoData")
ansi()
except: # can't load then ask to create it
ansi("red", True)
print options.file, "not found here. Create? (y/n) ",
ansi("yellow", True)
response = msvcrt.getch()
print response
ansi()
if response.upper() == "Y":
wb = excel.Workbooks.Add()
ws = excel.Worksheets.Add()
ws.Name = "MongoData"
wb.SaveAs(os.getcwd() + "\\" + options.file)
else: # don't wanna create it then exit
print "bye."
return
# see if ticks sheet works otherwise add it
try:
wst = wb.Worksheets("MongoTicks")
except:
wst = excel.Worksheets.Add()
wst.Name = "MongoTicks"
wst.Cells(3, 2).Value = 1
# see if securities list sheet works otherwise add it
try:
wall = wb.Worksheets("AllSecurities")
wall.Cells(1, 1).Value = "List of all securities"
wall.Range("A1:A1").Interior.ColorIndex = 8
wall.Range("A:A").ColumnWidth = 22
except:
wall = excel.Worksheets.Add()
wall.Name = "AllSecurities"
wall.Cells(1, 1).Value = "List of all securities"
wall.Range("A1:A1").Interior.ColorIndex = 8
wall.Range("A:A").ColumnWidth = 22
ansi("green", True)
print "talking to", options.file,
ansi("green", False)
print "... press any key when this console has the focus, to end communication"
ansi()
def linkstatusupdate():
global linkstatusperiod
if linkstatusperiod:
ws.Cells(1, 1).Value = "Talking to Python|"
wst.Cells(1, 1).Value = "Talking to Python!"
linkstatusperiod = False
else:
ws.Cells(1, 1).Value = "Talking to Python|"
wst.Cells(1, 1).Value = "Talking to Python!"
linkstatusperiod = True
ws.Cells(1, 2).Value = datetime.now()
# daily worksheet header formatting
ws.Cells(1, 1).Value = "Excel linked to Python"
ws.Cells(1, 3).Value = "Sort direction:"
ws.Cells(1, 4).Value = 1
ws.Cells(1, 5).Value = "Fetch max:"
ws.Cells(2, 1).Value = "Enter tickers:"
ws.Cells(3, 1).Value = "Start data:"
ws.Cells(4, 1).Value = "End data:"
ws.Range("A:A").ColumnWidth = 22
ws.Range("B:B").ColumnWidth = 20
ws.Range("A2:GS2").Interior.ColorIndex = 19 # beige 200 columns
ws.Range("A3:GS4").Interior.ColorIndex = 15 # grey
ws.Range("A2").Interior.ColorIndex = 3 # red
ws.Range("A3:A4").Interior.ColorIndex = 16 # dark grey
# minute worksheet header formatting
wst.Cells(1, 1).Value = "Excel linked to Python"
wst.Cells(2, 1).Value = "Enter tickers:"
#wst.Cells(3, 1).Value = "Enter periodicity:"
wst.Cells(1, 3).Value = "Sort direction:"
wst.Cells(1, 4).Value = 1
wst.Cells(1, 5).Value = "Fetch max:"
wst.Range("A:A").ColumnWidth = 22
wst.Range("B:B").ColumnWidth = 20
wst.Range("A2:GS3").Interior.ColorIndex = 19 # beige 200 columns
wst.Range("A4:GS5").Interior.ColorIndex = 15 # grey
wst.Range("A2:A3").Interior.ColorIndex = 4 # red
wst.Range("6:100000").Clear()
linkstatusperiod = False
oldsecd = []
oldseci = []
oldnumget = oldsortdir = toldnumget = toldsortdir = 0
while not msvcrt.kbhit():
try:
print "...", wb.Name,
securities = ws.Range("B2:GS2").Value[0]
sortdir = ws.Cells(1, 4).Value
if sortdir == None:
sortdir = 1
sortdir = int(sortdir)
numget = ws.Cells(1, 6).Value
if numget == None:
numget = 1000000
numget = int(numget)
securities = [x for x in securities if x is not None]
if not ((oldsecd == securities) and (oldnumget == numget) and (oldsortdir == sortdir)): # clear content of cells
ws.Range("5:1000000").Clear()
ws.Range("B3:GS4").Clear()
ws.Range("B3:GS4").Interior.ColorIndex = 15 # grey
oldsecd = securities
oldnumget = numget
oldsortdir = sortdir
currentcol = 0
for sec in securities:
linkstatusupdate()
secdata = mdaily(sec, "LAST_PRICE", sortdir, numget)
currentrow = 0
vallist = []
datelist = []
if sortdir == -1:
sortedkeys = sorted(secdata, reverse = True)
else:
sortedkeys = sorted(secdata, reverse = False)
for eachkey in sortedkeys:
datelist.append(eachkey)
vallist.append(secdata[eachkey])
#now stick them in Excel
ws.Range(offsetString(5 + currentrow, 2 + currentcol, 5 + currentrow + len(vallist) - 1, 2 + currentcol)).Value = \
tuple([(x, ) for x in vallist])
if currentcol == 0:
ws.Range(offsetString(5 + currentrow, 1, 5 + currentrow + len(vallist) - 1, 1)).Value = \
tuple([(x, ) for x in datelist])
if len(sortedkeys) > 0:
ws.Cells(3, 2 + currentcol).Value = sortedkeys[len(sortedkeys) - 1].split()[0] # start data date
ws.Cells(4, 2 + currentcol).Value = sortedkeys[0].split()[0] # end data date
currentcol += 1
# now do the tick data
securitiest = wst.Range("B2:GS2").Value[0]
securitiest = [x for x in securitiest if x is not None]
tsortdir = wst.Cells(1, 4).Value
if tsortdir == None:
tsortdir = 1
tsortdir = int(tsortdir)
tnumget = wst.Cells(1, 6).Value
if tnumget == None:
tnumget = 1000000
tnumget = int(tnumget)
if not ((oldseci == securitiest) and (toldnumget == tnumget) and (toldsortdir == tsortdir)): # clear the contents of the cells
wst.Range("6:1000000").Clear()
wst.Range("B4:GS5").Clear()
wst.Range("B4:GS5").Interior.ColorIndex = 15 # grey
oldseci = securitiest
toldnumget = tnumget
toldsortdir = tsortdir
secdata = mtick(securitiest, tsortdir, tnumget)
currentsec = 0
for x in secdata:
sender = [tuple(y[1:5]) for y in x]
wst.Range(offsetString(6, 2 + currentsec * 4, 6 + len(x) - 1, 5 + currentsec * 4)).Value = sender
if currentsec == 0: # then put the dates in
dates = [tuple([y[0], ]) for y in x]
wst.Range(offsetString(6, 1, 6 + len(x) - 1, 1)).Value = dates
wst.Range(offsetString(5, 2 + currentsec * 4, 5, 5 + currentsec * 4)).Value = ["open", "high", "low", "close"]
currentsec += 1
for x in range(0, len(securitiest)):
wst.Cells(4, 2 + x * 4).Value = securitiest[x]
linkstatusupdate()
allsecs = tuple([(yy, ) for yy in getsecs()])
wall.Range(offsetString(2, 1, len(allsecs) + 1, 1)).Value = allsecs
except:
print "\nExcel busy",
time.sleep(1)
endchar = msvcrt.getch() # capture the last character so it doesn't go to console
print "\nbye."
if __name__ == "__main__":
main()
