def import_aaii_exported_xls(xlrd_workbook, attribute_suffix="_aa"):
'''AAII .xls file'''
relevant_spreadsheet_list = utils.return_relevant_spreadsheet_list_from_workbook(
xlrd_workbook)
dict_list = []
# sample.xls specific
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
print ""
print "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py"
print "spreadsheet list > 1 sheet"
print ""
return None
spreadsheet = relevant_spreadsheet_list[0]
num_columns = spreadsheet.ncols
num_rows = spreadsheet.nrows
# important row and column numbers
attribute_name_row = 0 # 1st row
ticker_col = 1 # 2nd column
for row_num in range(num_rows):
if row_num in [0]: # attribute row
continue
dict_to_add = {}
for col_num in range(num_columns):
datum = utils.return_xls_cell_value(xlrd_spreadsheet=spreadsheet,
row=row_num,
column=col_num)
attribute_name = utils.return_xls_cell_value(
xlrd_spreadsheet=spreadsheet,
row=attribute_name_row,
column=col_num)
if not datum:
datum = "-"
if col_num == ticker_col:
dict_to_add = {"stock": str(datum)}
else:
if attribute_name:
dict_to_add[attribute_name] = datum
else:
print "Error: wxStocks_xls_import_functions: function: process_sample_dot_xls: Data present with no named attribute."
if dict_to_add:
dict_list.append(dict_to_add)
print dict_list
return (dict_list, attribute_suffix)
# end of line
def process_sample_dot_xls(xlrd_workbook, attribute_suffix = "_xl"):
'''import sample.xls'''
import config
from wxStocks_modules import wxStocks_utilities as utils
relevant_spreadsheet_list = utils.return_relevant_spreadsheet_list_from_workbook(xlrd_workbook)
dict_list = []
# sample.xls specific
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
print ""
print "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py"
print "spreadsheet list > 1 sheet"
print ""
return None
spreadsheet = relevant_spreadsheet_list[0]
num_columns = spreadsheet.ncols
num_rows = spreadsheet.nrows
# important row and column numbers
attribute_name_row = 2 # 3rd row
ticker_col = 0 # 1st column
for row_num in range(num_rows):
if row_num in [0, 1, 2]:
continue
dict_to_add = {}
for col_num in range(num_columns):
datum = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = col_num)
attribute_name = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = attribute_name_row, column = col_num)
if datum:
if col_num == ticker_col:
dict_to_add = {"stock": datum}
else:
if attribute_name:
dict_to_add[attribute_name] = datum
else:
print "Error: wxStocks_xls_import_functions: function: process_sample_dot_xls: Data present with no named attribute."
if dict_to_add:
dict_list.append(dict_to_add)
print dict_list
return (dict_list, attribute_suffix)
# end of line
def import_aaii_exported_xls(xlrd_workbook, attribute_suffix = "_aa"):
'''AAII .xls file'''
relevant_spreadsheet_list = utils.return_relevant_spreadsheet_list_from_workbook(xlrd_workbook)
dict_list = []
# sample.xls specific
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
print ""
print "Error in process_sample_dot_xls() in wxStocks_xls_import_functions.py"
print "spreadsheet list > 1 sheet"
print ""
return None
spreadsheet = relevant_spreadsheet_list[0]
num_columns = spreadsheet.ncols
num_rows = spreadsheet.nrows
# important row and column numbers
attribute_name_row = 0 # 1st row
ticker_col = 1 # 2nd column
for row_num in range(num_rows):
if row_num in [0]: # attribute row
continue
dict_to_add = {}
for col_num in range(num_columns):
datum = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = col_num)
attribute_name = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = attribute_name_row, column = col_num)
if not datum:
datum = "-"
if col_num == ticker_col:
dict_to_add = {"stock": str(datum)}
else:
if attribute_name:
dict_to_add[attribute_name] = datum
else:
print "Error: wxStocks_xls_import_functions: function: process_sample_dot_xls: Data present with no named attribute."
if dict_to_add:
dict_list.append(dict_to_add)
print dict_list
return (dict_list, attribute_suffix)
# end of line
def sample_xls(xls_file, attribute_suffix = "_my"):
"""Sample XLS portfolio import"""
if not xlrd:
raise Exception("Module XLRD is not installed on your machine, please install it")
return
import config
from wxStocks_modules import wxStocks_utilities as utils
xlrd_workbook = xlrd.open_workbook(xls_file.name)
relevant_spreadsheet_list = utils.return_relevant_spreadsheet_list_from_workbook(xlrd_workbook)
dict_list = []
# sample.xls specific
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
raise Exception("\nError in default_xls() in wxStocks_portfolio_import_functions.py\nspreadsheet list > 1 sheet\n")
return None
spreadsheet = relevant_spreadsheet_list[0]
num_columns = spreadsheet.ncols
num_rows = spreadsheet.nrows
#print line_number(), self.portfolio_data
new_account_stock_list = []
cash = "This should be changed"
cost_basis_dict = {}
count = 0
ticker_col = None
ticker_vars = ["ticker", "symbol"]
quantity_col = None
quantity_vars = ["quantity"]
cost_basis_col = None
cost_basis_vars = ["costbasis", "cost basis", "cost_basis"]
market_value_col = None
market_value_vars = ["marketvalue", "market value", "market_value"]
cash_row = None
cash_vars = ["Cash", "cost basis", "cost_basis"]
for row_num in range(num_rows):
for col_num in range(num_columns):
cell_value = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = col_num)
cell_value = str(cell_value)
# quick parser for keywords
if ticker_col is None:
for var in ticker_vars:
if var in cell_value.lower():
ticker_col = col_num
if quantity_col is None:
for var in quantity_vars:
if var in cell_value.lower():
quantity_col = col_num
if cost_basis_col is None:
for var in cost_basis_vars:
if var in cell_value.lower():
cost_basis_col = col_num
if market_value_col is None:
for var in market_value_vars:
if var in cell_value.lower():
market_value_col = col_num
if cash_row is None:
for var in cash_vars:
if var in cell_value:
cash_row = row_num
# Go!
if ticker_col is not None and quantity_col is not None:
for row_num in range(num_rows):
if cash_row is not None:
if row_num == cash_row:
cash = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = market_value_col)
formatted_cash = ""
for char in cash:
if char == "$" or char == " ":
pass
else:
formatted_cash += char
cash = float(formatted_cash)
ticker = None
possible_ticker = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = ticker_col)
if possible_ticker == possible_ticker.upper():
ticker = possible_ticker
if ticker:
stock_shares_tuple = (ticker, utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = quantity_col))
new_account_stock_list.append(stock_shares_tuple)
if cost_basis_col is not None:
basis = utils.return_xls_cell_value(xlrd_spreadsheet = spreadsheet, row = row_num, column = cost_basis_col)
formatted_basis = ""
for char in basis:
if char == "$" or char == " ":
pass
else:
formatted_basis += char
basis = float(formatted_basis)
cost_basis_dict[ticker] = basis
if cash == "This should be changed":
logging.error('Formatting error in CSV import')
account_dict = {"cash": cash, "stock_list": new_account_stock_list, "cost_basis_dict": cost_basis_dict}
if not account_dict:
logging.error("Error: empty account dictionary to return.")
return
return account_dict
return (dict_list, attribute_suffix)
def sample_xls(xls_file, attribute_suffix="_my"):
"""Sample XLS portfolio import"""
if not xlrd:
raise Exception(
"Module XLRD is not installed on your machine, please install it")
return
import config
from wxStocks_modules import wxStocks_utilities as utils
xlrd_workbook = xlrd.open_workbook(xls_file.name)
relevant_spreadsheet_list = utils.return_relevant_spreadsheet_list_from_workbook(
xlrd_workbook)
dict_list = []
# sample.xls specific
# only 1 sheet
if not len(relevant_spreadsheet_list) == 1:
raise Exception(
"\nError in default_xls() in wxStocks_portfolio_import_functions.py\nspreadsheet list > 1 sheet\n"
)
return None
spreadsheet = relevant_spreadsheet_list[0]
num_columns = spreadsheet.ncols
num_rows = spreadsheet.nrows
#print line_number(), self.portfolio_data
new_account_stock_list = []
cash = "This should be changed"
cost_basis_dict = {}
count = 0
ticker_col = None
ticker_vars = ["ticker", "symbol"]
quantity_col = None
quantity_vars = ["quantity"]
cost_basis_col = None
cost_basis_vars = ["costbasis", "cost basis", "cost_basis"]
market_value_col = None
market_value_vars = ["marketvalue", "market value", "market_value"]
cash_row = None
cash_vars = ["Cash", "cost basis", "cost_basis"]
for row_num in range(num_rows):
for col_num in range(num_columns):
cell_value = utils.return_xls_cell_value(
xlrd_spreadsheet=spreadsheet, row=row_num, column=col_num)
cell_value = str(cell_value)
# quick parser for keywords
if ticker_col is None:
for var in ticker_vars:
if var in cell_value.lower():
ticker_col = col_num
if quantity_col is None:
for var in quantity_vars:
if var in cell_value.lower():
quantity_col = col_num
if cost_basis_col is None:
for var in cost_basis_vars:
if var in cell_value.lower():
cost_basis_col = col_num
if market_value_col is None:
for var in market_value_vars:
if var in cell_value.lower():
market_value_col = col_num
if cash_row is None:
for var in cash_vars:
if var in cell_value:
cash_row = row_num
# Go!
if ticker_col is not None and quantity_col is not None:
for row_num in range(num_rows):
if cash_row is not None:
if row_num == cash_row:
cash = utils.return_xls_cell_value(
xlrd_spreadsheet=spreadsheet,
row=row_num,
column=market_value_col)
formatted_cash = ""
for char in cash:
if char == "$" or char == " ":
pass
else:
formatted_cash += char
cash = float(formatted_cash)
ticker = None
possible_ticker = utils.return_xls_cell_value(
xlrd_spreadsheet=spreadsheet, row=row_num, column=ticker_col)
if possible_ticker == possible_ticker.upper():
ticker = possible_ticker
if ticker:
stock_shares_tuple = (ticker,
utils.return_xls_cell_value(
xlrd_spreadsheet=spreadsheet,
row=row_num,
column=quantity_col))
new_account_stock_list.append(stock_shares_tuple)
if cost_basis_col is not None:
basis = utils.return_xls_cell_value(
xlrd_spreadsheet=spreadsheet,
row=row_num,
column=cost_basis_col)
formatted_basis = ""
for char in basis:
if char == "$" or char == " ":
pass
else:
formatted_basis += char
basis = float(formatted_basis)
cost_basis_dict[ticker] = basis
if cash == "This should be changed":
logging.error('Formatting error in CSV import')
account_dict = {
"cash": cash,
"stock_list": new_account_stock_list,
"cost_basis_dict": cost_basis_dict
}
if not account_dict:
logging.error("Error: empty account dictionary to return.")
return
return account_dict
return (dict_list, attribute_suffix)
