def process_aaii_xls_key_file(filename):
'grabs the long attribute names to map onto stock objects'
xlrd_workbook = xlrd.open_workbook(filename)
relevant_spreadsheet_list = return_relevant_spreadsheet_list_from_workbook(xlrd_workbook)
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
print line_number(), ""
print line_number(), "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py"
print line_number(), "spreadsheet list > 1 sheet"
print line_number(), ""
return None
spreadsheet = relevant_spreadsheet_list[0]
spreadsheet_list_of_row_data = []
for i in range(spreadsheet.nrows):
row_list = spreadsheet.row_values(i)
spreadsheet_list_of_row_data.append(row_list)
key_dict = {}
for row_list in spreadsheet_list_of_row_data[1:]:
key_dict[row_list[0]] = row_list[1]
#print line_number()
#pprint.pprint(key_dict)
return key_dict
def process_aaii_xls_key_file(filename):
'grabs the long attribute names to map onto stock objects'
xlrd_workbook = xlrd.open_workbook(filename)
relevant_spreadsheet_list = return_relevant_spreadsheet_list_from_workbook(
xlrd_workbook)
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
print line_number(), ""
print line_number(
), "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py"
print line_number(), "spreadsheet list > 1 sheet"
print line_number(), ""
return None
spreadsheet = relevant_spreadsheet_list[0]
spreadsheet_list_of_row_data = []
for i in range(spreadsheet.nrows):
row_list = spreadsheet.row_values(i)
spreadsheet_list_of_row_data.append(row_list)
key_dict = {}
for row_list in spreadsheet_list_of_row_data[1:]:
key_dict[row_list[0]] = row_list[1]
#print line_number()
#pprint.pprint(key_dict)
return key_dict
def process_aaii_xls_data_file(filename, key_dict):
print line_number(), filename, "Start!"
xlrd_workbook = xlrd.open_workbook(filename)
relevant_spreadsheet_list = return_relevant_spreadsheet_list_from_workbook(
xlrd_workbook)
ticker_column_string = u"ticker"
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
print line_number(), ""
print line_number(
), "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py"
print line_number(), "spreadsheet list > 1 sheet"
print line_number(), ""
sys.exit()
spreadsheet = relevant_spreadsheet_list[0]
num_columns = spreadsheet.ncols
num_rows = spreadsheet.nrows
# important row and column numbers
top_row = 0 # 1st row
spreadsheet_list_of_row_data = []
for i in range(spreadsheet.nrows):
row_list = spreadsheet.row_values(i)
spreadsheet_list_of_row_data.append(row_list)
ticker_location = spreadsheet_list_of_row_data[0].index(u'ticker')
#print line_number(), spreadsheet_list_of_row_data[0]
current_time = time.time()
for row_list in spreadsheet_list_of_row_data[1:]:
ticker = str(row_list[ticker_location])
current_stock = db.create_new_Stock_if_it_doesnt_exist(ticker)
for attribute in spreadsheet_list_of_row_data[0]:
# get the shortened attribute name here
attribute_index = spreadsheet_list_of_row_data[0].index(attribute)
if attribute_index == ticker_location:
continue
long_attribute_name = key_dict.get(attribute.upper())
#print line_number(), long_attribute_name
long_attribute_name = remove_inappropriate_characters_from_attribute_name(
long_attribute_name)
#print line_number(), long_attribute_name
datum = row_list[attribute_index]
db.set_Stock_attribute(current_stock, long_attribute_name, datum,
"_aa")
# set last update time
if not current_stock.firm_name:
try:
current_stock.firm_name = current_stock.Company_name_aa
except:
pass
current_stock.last_aaii_update_aa = current_time
def process_aaii_xls_data_file(filename, key_dict): print line_number(), filename, "Start!" xlrd_workbook = xlrd.open_workbook(filename) relevant_spreadsheet_list = return_relevant_spreadsheet_list_from_workbook(xlrd_workbook) ticker_column_string = u"ticker" # only 1 sheet if not len(relevant_spreadsheet_list) == 1: print line_number(), "" print line_number(), "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py" print line_number(), "spreadsheet list > 1 sheet" print line_number(), "" sys.exit() spreadsheet = relevant_spreadsheet_list[0] num_columns = spreadsheet.ncols num_rows = spreadsheet.nrows # important row and column numbers top_row = 0 # 1st row spreadsheet_list_of_row_data = [] for i in range(spreadsheet.nrows): row_list = spreadsheet.row_values(i) spreadsheet_list_of_row_data.append(row_list) ticker_location = spreadsheet_list_of_row_data[0].index(u'ticker') #print line_number(), spreadsheet_list_of_row_data[0] current_time = time.time() for row_list in spreadsheet_list_of_row_data[1:]: ticker = str(row_list[ticker_location]) current_stock = db.create_new_Stock_if_it_doesnt_exist(ticker) for attribute in spreadsheet_list_of_row_data[0]: # get the shortened attribute name here attribute_index = spreadsheet_list_of_row_data[0].index(attribute) if attribute_index == ticker_location: continue long_attribute_name = key_dict.get(attribute.upper()) #print line_number(), long_attribute_name long_attribute_name = remove_inappropriate_characters_from_attribute_name(long_attribute_name) #print line_number(), long_attribute_name datum = row_list[attribute_index] db.set_Stock_attribute(current_stock, long_attribute_name, datum, "_aa") # set last update time if not current_stock.firm_name: try: current_stock.firm_name = current_stock.Company_name_aa except: pass current_stock.last_aaii_update_aa = current_time
def import_xls_via_user_created_function(wxWindow, user_created_function):
'''adds import csv data to stocks data via a user created function'''
try:
from modules import xlrd
except:
logging.error(
"Error: cannot import xls file because xlrd module failed to load")
return
dirname = ''
dialog = wx.FileDialog(wxWindow, "Choose a file", dirname, "", "*.xls",
wx.OPEN)
if dialog.ShowModal() == wx.ID_OK:
filename = dialog.GetFilename()
dirname = dialog.GetDirectory()
#csv_file = open(os.path.join(dirname, filename), 'rb')
xls_workbook = xlrd.open_workbook(dirname + "/" + filename)
dict_list_and_attribute_suffix_tuple = user_created_function(
xls_workbook)
else:
dialog.Destroy()
return
dialog.Destroy()
# process returned tuple
attribute_suffix = dict_list_and_attribute_suffix_tuple[1]
success = None
if len(attribute_suffix) != 3 or attribute_suffix[0] != "_":
logging.error("Error: your attribute suffix is improperly formatted")
success = "fail"
return success
dict_list = dict_list_and_attribute_suffix_tuple[0]
for this_dict in dict_list:
if not this_dict:
continue
try:
this_dict['stock']
except Exception as e:
logging.error(e)
logging.error(
"Error: your dictionary does not have the ticker as your_dictionary['stock']"
)
if success in ["success", "some"]:
success = "some"
else:
success = "fail"
continue
stock = utils.return_stock_by_symbol(this_dict['stock'])
if not stock:
logging.error(
"Error: your_dictionary['stock'] does not return a recognized ticker symbol."
)
if success in ["success", "some"]:
success = "some"
else:
success = "fail"
continue
for key, value in this_dict.items():
if key == "stock":
continue
else:
setattr(stock, key + attribute_suffix, value)
if success in ["fail", "some"]:
success = "some"
else:
success = "success"
return success
from modules import xlrd xls_files = ["sample_aaii.xls", "tiny.xls"] test_file = xls_files[1] workbook = xlrd.open_workbook(test_file) print "The number of worksheets is", workbook.nsheets def return_relevant_spreadsheet_list_from_workbook(xlrd_workbook): relevant_sheets = [] for i in range(workbook.nsheets): sheet = workbook.sheet_by_index(i) print sheet.name if sheet.nrows or sheet.ncols: print "rows x cols:", sheet.nrows, sheet.ncols relevant_sheets.append(sheet) else: print "is empty" print "" return relevant_sheets def return_xls_cell_value(xlrd_spreadsheet, row, column): return xlrd_spreadsheet.cell_value(rowx=row, colx=column) relevant_sheets = return_relevant_spreadsheet_list_from_workbook(workbook) print "The number of relevant worksheets is", len(relevant_sheets) print "Worksheet name(s):", [x.name for x in relevant_sheets]
def import_xls_via_user_created_function(wxWindow, user_created_function): '''adds import csv data to stocks data via a user created function''' try: from modules import xlrd except: print line_number(), "Error: cannot import xls file because xlrd module failed to load" return dirname = '' dialog = wx.FileDialog(wxWindow, "Choose a file", dirname, "", "*.xls", wx.OPEN) if dialog.ShowModal() == wx.ID_OK: filename = dialog.GetFilename() dirname = dialog.GetDirectory() #csv_file = open(os.path.join(dirname, filename), 'rb') xls_workbook = xlrd.open_workbook(dirname + "/" + filename) dict_list_and_attribute_suffix_tuple = user_created_function(xls_workbook) else: dialog.Destroy() return dialog.Destroy() # process returned tuple attribute_suffix = dict_list_and_attribute_suffix_tuple[1] success = None if len(attribute_suffix) != 3 or attribute_suffix[0] != "_": print line_number(), "Error: your attribute suffix is improperly formatted" success = "fail" return success dict_list = dict_list_and_attribute_suffix_tuple[0] for this_dict in dict_list: if not this_dict: continue try: this_dict['stock'] except Exception as e: print line_number(), e print line_number(), "Error: your dictionary does not have the ticker as your_dictionary['stock']" if success in ["success", "some"]: success = "some" else: success = "fail" continue stock = utils.return_stock_by_symbol(this_dict['stock']) if not stock: print line_number(), "Error: your_dictionary['stock'] does not return a recognized ticker symbol." if success in ["success", "some"]: success = "some" else: success = "fail" continue for key, value in this_dict.iteritems(): if key == "stock": continue else: setattr(stock, key + attribute_suffix, value) if success in ["fail", "some"]: success = "some" else: success = "success" return success
from modules import xlrd
xls_files = ["sample_aaii.xls", "tiny.xls"]
test_file = xls_files[1]
workbook = xlrd.open_workbook(test_file)
print "The number of worksheets is", workbook.nsheets
def return_relevant_spreadsheet_list_from_workbook(xlrd_workbook):
relevant_sheets = []
for i in range(workbook.nsheets):
sheet = workbook.sheet_by_index(i)
print sheet.name
if sheet.nrows or sheet.ncols:
print "rows x cols:", sheet.nrows, sheet.ncols
relevant_sheets.append(sheet)
else:
print "is empty"
print ""
return relevant_sheets
def return_xls_cell_value(xlrd_spreadsheet, row, column):
return xlrd_spreadsheet.cell_value(rowx=row, colx=column)
relevant_sheets = return_relevant_spreadsheet_list_from_workbook(workbook)
print "The number of relevant worksheets is", len(relevant_sheets)
