def main():
global SHEET_NUMBER, STARTING_ROW, wb, fields_dictionary
global sheet_dict, date_, company_type
INPUT_FILE = "finance_old.xls"
OUTPUT_FILE = os.path.join(common.OUTPUT_FOLDER, 'transfered_property')
VARIABLES_FILE = 'sheet7.csv'
DIGITS_ROW_NUMBER = 6
STARTING_ROW = 8
SHEET_NUMBER = 7
DATE_FILE = "finance_date.txt"
QUARTERS_DICT = {"01": 12, "04": 3, "07": 6, "10": 9}
with open(VARIABLES_FILE, 'r') as vf:
var_reader = csv.reader(vf)
fields_dictionary = {}
for l in var_reader:
fields_dictionary[l[0]] = l[2]
sheet_dict = {}
headers = []
keys_num = [int(k) for k in fields_dictionary.keys()]
keys_num = sorted(keys_num)
for k in keys_num:
sheet_dict[fields_dictionary[str(k)]] = []
headers.append(fields_dictionary[str(k)])
with open(common.CURRENT_FILENAME, "r") as cff:
input_file = cff.read()
wb = xlrd.open_workbook(input_file, formatting_info=True)
sheet_dict["company_type"] = []
company_type = ''
load_workbook(wb)
sheet_dict["company_code"] = list(
map(format_code, sheet_dict["company_code"]))
with open(DATE_FILE, "r") as df:
date_ = df.read()
date_datetime = datetime.datetime.strptime(date_, "%d.%m.%Y")
if date_datetime.month == 1:
sheet_dict["year"] = [int(date_[-4:]) - 1] * len(
sheet_dict["company_code"])
else:
sheet_dict["year"] = [int(date_[-4:])] * len(
sheet_dict["company_code"])
sheet_dict["period"] = [QUARTERS_DICT[date_[3:5]]] * len(
sheet_dict["company_code"])
sheet_dict = common.add_company_status(sheet_dict)
sheet_dict['branch'] = list(
map(common.extract_branch, sheet_dict['company_name']))
sheet_dict['company_name'] = [
sheet_dict['company_name'][i].replace(sheet_dict['branch'][i],
"").strip()
for i in range(len(sheet_dict['company_name']))
if sheet_dict['branch'] != ''
]
for i in range(len(sheet_dict['branch'])):
if sheet_dict['branch'][i] != '':
sheet_dict['branch'][i] = sheet_dict['branch'][i][1:-1]
headers = ['year', 'period'] + headers[:2] + [
"company_type", "company_status", "branch"
] + headers[2:]
finance_list = dict_to_list(sheet_dict, headers)
with open(OUTPUT_FILE + common.filename_part(date_datetime) + ".csv",
"w",
newline="") as of:
csvwriter = csv.writer(of)
csvwriter.writerow(headers)
for l in finance_list:
if l[0] != "":
line_to_write = l[:]
if isinstance(line_to_write[2], float):
d = xlrd.xldate_as_tuple(line_to_write[2], wb.datemode)
d = datetime.datetime(*d[0:6]).strftime("%d.%m.%Y")
line_to_write[2] = d
csvwriter.writerow(line_to_write)
out_wb = xlsxwriter.Workbook(OUTPUT_FILE +
common.filename_part(date_datetime) + ".xlsx")
worksheet = out_wb.add_worksheet()
datef = out_wb.add_format({'num_format': "dd.mm.yyyy"})
numf = out_wb.add_format({'num_format': "0.00"})
headerf = out_wb.add_format({'bold': True})
for i in range(len(headers)):
worksheet.write(0, i, headers[i], headerf)
for i in range(len(finance_list)):
for j in range(len(headers)):
if j > 6:
worksheet.write(i + 1, j, finance_list[i][j], numf)
elif j == 6:
worksheet.write(i + 1, j, finance_list[i][j], datef)
else:
worksheet.write(i + 1, j, finance_list[i][j])
out_wb.close()
def main():
global STATUSES, SHEET_NUMBER, STARTING_ROW, DATE_RE, wb, fields_dictionary
global ST_DECISION_RE, sheet_dict, date_, company_type
OUTPUT_FILE = os.path.join(common.OUTPUT_FOLDER, 'companies_registry')
DATE_FILE = "finance_date.txt"
COMPANY_STATUSES_FILE = "company_statuses.json"
VARIABLES_FILE = 'sheet0.csv'
DIGITS_ROW_NUMBER = 5
STARTING_ROW = 7
SHEET_NUMBER = 0
STATUSES = ["перебуває у процедурі банкрутства", "перебуває у процедурі реорганізації", "перебуває у процедурі у ліквідації за рішенням органу влади"]
ST_DECISION_RE = re.compile("\((?P<status_decision>.*)\)")
DATE_RE = re.compile("\d{2}\.\d{2}\.\d{4}")
QUARTERS_DICT = {"01": 12, "04": 3, "07": 6, "10": 9}
with open(VARIABLES_FILE, 'r') as vf:
var_reader = csv.reader(vf)
fields_dictionary = {}
for l in var_reader:
fields_dictionary[l[0]] = l[2]
sheet_dict = {}
headers = []
keys_num = [int(k) for k in fields_dictionary.keys()]
keys_num = sorted(keys_num)
for k in keys_num:
sheet_dict[fields_dictionary[str(k)]] = []
headers.append(fields_dictionary[str(k)])
headers = headers[:9] + ['non_working_status_type', 'non_working_status_decision'] + headers[9:]
headers = headers[:3] + ["company_type"] + headers[3:]
sheet_dict['non_working_status_type'] = []
sheet_dict['non_working_status_decision'] = []
sheet_dict['company_type'] = []
company_type = ''
with open(common.CURRENT_FILENAME, "r") as cff:
input_file = cff.read()
wb = xlrd.open_workbook(input_file, formatting_info = True)
load_workbook(wb)
sheet_dict['region_code'] = list(map(format_region_code, sheet_dict['region_code']))
sheet_dict['company_code'] = list(map(format_code, sheet_dict['company_code']))
sheet_dict['is_working'] = list(map(is_working_extend, sheet_dict['is_working']))
statuses_dict = combine(sheet_dict)
with open(COMPANY_STATUSES_FILE, "w") as csf:
json.dump(statuses_dict, csf)
with open(DATE_FILE, "w") as df:
df.write(date_)
date_datetime = datetime.datetime.strptime(date_, "%d.%m.%Y")
headers = ["year", "period"] + headers
if date_datetime.month == 1:
sheet_dict["year"] = [int(date_[-4:]) - 1] * len(sheet_dict["company_code"])
else:
sheet_dict["year"] = [int(date_[-4:])] * len(sheet_dict["company_code"])
sheet_dict["period"] = [QUARTERS_DICT[date_[3:5]]] * len(sheet_dict["company_code"])
finance_list = dict_to_list(sheet_dict, headers)
with open(OUTPUT_FILE + common.filename_part(date_datetime) + ".csv", "w", newline = "") as of:
csvwriter = csv.writer(of)
csvwriter.writerow(headers)
for l in finance_list:
if l[1] != "":
line_to_write = l[:]
#line_to_write[0] = datetime.datetime.strftime(line_to_write[0], "%d.%m.%Y")
csvwriter.writerow(line_to_write)
out_wb = xlsxwriter.Workbook(OUTPUT_FILE + common.filename_part(date_datetime) + ".xlsx")
worksheet = out_wb.add_worksheet()
datef = out_wb.add_format({'num_format':"dd.mm.yyyy"})
numf = out_wb.add_format({'num_format':"0.00"})
headerf = out_wb.add_format({'bold':True})
for i in range(len(headers)):
worksheet.write(0, i, headers[i], headerf)
for i in range(len(finance_list)):
for j in range(len(headers)):
if j > 11 and j < 15:
worksheet.write(i+1, j, finance_list[i][j], numf)
else:
worksheet.write(i+1, j, finance_list[i][j])
out_wb.close()
def main():
global SHEET_NUMBER, STARTING_ROW, wb, fields_dictionary, FINAL_MARKS
global sheet_dict, date_, company_type
OUTPUT_FILE = os.path.join(common.OUTPUT_FOLDER, 'assessment')
VARIABLES_FILE = 'sheet5.csv'
DIGITS_ROW_NUMBER = 6
STARTING_ROW = 6
SHEET_NUMBER = 5
FINAL_MARKS = ["ефективна", "задовільна", "неефективна"]
DATE_FILE = "finance_date.txt"
QUARTERS_DICT = {"01": 12, "04": 3, "07": 6, "10": 9}
with open(VARIABLES_FILE, 'r') as vf:
var_reader = csv.reader(vf)
fields_dictionary = {}
for l in var_reader:
fields_dictionary[l[0]] = l[2]
sheet_dict = {}
headers = []
keys_num = [int(k) for k in fields_dictionary.keys()]
keys_num = sorted(keys_num)
for k in keys_num:
sheet_dict[fields_dictionary[str(k)]] = []
headers.append(fields_dictionary[str(k)])
sheet_dict['final_assessment'] = []
headers.append('final_assessment')
with open(common.CURRENT_FILENAME, "r") as cff:
input_file = cff.read()
wb = xlrd.open_workbook(input_file, formatting_info = True)
company_type = "державне підприємство"
company_type = ""
sheet_dict["company_type"] = []
load_workbook(wb)
sheet_dict["company_code"] = list(map(format_code, sheet_dict["company_code"]))
with open(DATE_FILE, "r") as df:
date_ = df.read()
date_datetime = datetime.datetime.strptime(date_, "%d.%m.%Y")
if date_datetime.month == 1:
sheet_dict["year"] = [int(date_[-4:]) - 1] * len(sheet_dict["company_code"])
else:
sheet_dict["year"] = [int(date_[-4:])] * len(sheet_dict["company_code"])
sheet_dict["period"] = [QUARTERS_DICT[date_[3:5]]] * len(sheet_dict["company_code"])
sheet_dict = common.add_company_status(sheet_dict)
headers = ['year', 'period'] + headers[:2] + ["company_type", "company_status"] + headers[2:]
finance_list = dict_to_list(sheet_dict, headers)
with open(OUTPUT_FILE + common.filename_part(date_datetime) + ".csv", "w", newline="") as of:
csvwriter = csv.writer(of)
csvwriter.writerow(headers)
for l in finance_list:
if l[0] != "":
line_to_write = l[:]
csvwriter.writerow(line_to_write)
out_wb = xlsxwriter.Workbook(OUTPUT_FILE + common.filename_part(date_datetime) + ".xlsx")
worksheet = out_wb.add_worksheet()
datef = out_wb.add_format({'num_format':"dd.mm.yyyy"})
numf = out_wb.add_format({'num_format':"0.00"})
headerf = out_wb.add_format({'bold':True})
for i in range(len(headers)):
worksheet.write(0, i, headers[i], headerf)
for i in range(len(finance_list)):
for j in range(len(headers)):
if j > 5:
worksheet.write(i+1, j, finance_list[i][j], numf)
else:
worksheet.write(i+1, j, finance_list[i][j])
out_wb.close()
def main():
global SHEET_NUMBER, STARTING_ROW, DATE_RE, wb, fields_dictionary
global sheet_dict, date_, company_type
INPUT_FILE = "finance_old.xls"
OUTPUT_FILE = os.path.join(common.OUTPUT_FOLDER, 'financial_plans')
VARIABLES_FILE = 'sheet1.csv'
DATE_FILE = "finance_date.txt"
DIGITS_ROW_NUMBER = 6
STARTING_ROW = 7
SHEET_NUMBER = 1
DATE_RE = re.compile("\d{2}\.\d{2}\.\d{4}")
QUARTERS_DICT = {"01": 12, "04": 3, "07": 6, "10": 9}
HEADERS = [
"year",
"period",
"company_name",
"company_code",
"company_type",
"company_status",
"fin_plan_adopted",
"fin_plan_adoption_date",
'fin_plan_changes_dates',
"sales_revenue_plan",
"sales_revenue_fact",
"finance_result_plan",
"finance_result_fact",
"revenue_to_budget_plan",
"revenue_to_budget_fact",
"revenue_to_state_shareholder_plan",
"revenue_to_state_shareholder_fact",
"capital_investments_plan",
"capital_investments_fact",
]
with open(VARIABLES_FILE, 'r') as vf:
var_reader = csv.reader(vf)
fields_dictionary = {}
for l in var_reader:
fields_dictionary[l[0]] = l[2]
sheet_dict = {}
headers = []
keys_num = [int(k) for k in fields_dictionary.keys()]
keys_num = sorted(keys_num)
for k in keys_num:
sheet_dict[fields_dictionary[str(k)]] = []
headers.append(fields_dictionary[str(k)])
with open(common.CURRENT_FILENAME, "r") as cff:
input_file = cff.read()
wb = xlrd.open_workbook(input_file, formatting_info=True)
company_type = ''
sheet_dict["company_type"] = []
sheet_dict["company_status"] = []
load_workbook(wb)
sheet_dict["company_code"] = list(
map(format_code, sheet_dict["company_code"]))
sheet_dict["fin_plan_adoption_date"] = list(
map(plan_date_refine, sheet_dict["fin_plan_adoption_date"]))
sheet_dict['fin_plan_changes_dates'] = list(
map(extract_changes_date, sheet_dict["fin_plan_adoption_date"]))
sheet_dict["fin_plan_adoption_date"] = list(
map(string_to_date, sheet_dict["fin_plan_adoption_date"]))
sheet_dict['fin_plan_adopted'] = list(
map(lambda s: s.lower().strip(), sheet_dict['fin_plan_adopted']))
sheet_dict = common.add_company_status(sheet_dict)
with open(DATE_FILE, "r") as df:
date_ = df.read()
date_datetime = datetime.datetime.strptime(date_, "%d.%m.%Y")
headers = ["year", "period"] + headers
if date_datetime.month == 1:
sheet_dict["year"] = [int(date_[-4:]) - 1] * len(
sheet_dict["company_code"])
else:
sheet_dict["year"] = [int(date_[-4:])] * len(
sheet_dict["company_code"])
sheet_dict["period"] = [QUARTERS_DICT[date_[3:5]]] * len(
sheet_dict["company_code"])
finance_list = dict_to_list(sheet_dict, HEADERS)
with open(OUTPUT_FILE + common.filename_part(date_datetime) + ".csv",
"w",
newline="") as of:
csvwriter = csv.writer(of)
csvwriter.writerow(HEADERS)
for l in finance_list:
if l[0] != "":
line_to_write = l[:]
if line_to_write[7]:
line_to_write[7] = datetime.datetime.strftime(
line_to_write[7], "%d.%m.%Y")
csvwriter.writerow(line_to_write)
out_wb = xlsxwriter.Workbook(OUTPUT_FILE +
common.filename_part(date_datetime) + ".xlsx")
worksheet = out_wb.add_worksheet()
datef = out_wb.add_format({'num_format': "dd.mm.yyyy"})
numf = out_wb.add_format({'num_format': "0.00"})
headerf = out_wb.add_format({'bold': True})
for i in range(len(HEADERS)):
worksheet.write(0, i, HEADERS[i], headerf)
for i in range(len(finance_list)):
for j in range(len(HEADERS)):
if finance_list[i][0] != "":
if j == 7:
worksheet.write(i + 1, j, finance_list[i][j], datef)
elif j > 8:
worksheet.write(i + 1, j, finance_list[i][j], numf)
else:
worksheet.write(i + 1, j, finance_list[i][j])
out_wb.close()