def add(self, obj):
"""
Container.add() -> None
--``obj`` is an instance of obj
Method checks that obj has a valid name and ID and that these
attributes do not conflict with existing entries before adding the
new obj.
"""
# Make sure this obj is valid
if not obj.id.bbid:
c = "Cannot add obj that does not have a valid bbid."
raise bb_exceptions.IDError(c)
if not obj.name:
c = "Cannot add obj that does not have a valid name."
raise bb_exceptions.BBAnalyticalError(c)
if obj.id.bbid in self.directory:
c = "Cannot overwrite existing obj. obj with id %s already" \
" exists in directory." % obj.id.bbid.hex
raise bb_exceptions.BBAnalyticalError(c)
if obj.name in self.by_name:
c = "Cannot overwrite existing obj. obj named %s already" \
" exists in directory." % obj.name
raise bb_exceptions.BBAnalyticalError(c)
self.directory[obj.id.bbid] = obj
self.by_name[obj.name.casefold()] = obj.id.bbid
def _inspect_line_for_insertion(self, line):
"""
BaseFinancialComponent._inspect_line_for_insertion() -> None
Will throw exception if Line if you can't insert line into instance.
"""
if not line.tags.name:
c = "Cannot add nameless lines."
raise bb_exceptions.BBAnalyticalError(c)
if line.tags.name in self._details:
c = "Implicit overwrites prohibited: {}".format(line.tags.name)
raise bb_exceptions.BBAnalyticalError(c)
def make_batches_linearly(start_dt, end_dt, template, start_num, number, batches):
"""
make_batches_linearly() -> List
Returns a list of newly created objects with uniform birth dates.
Function is designed to be used if number > 50. Instead of creating 50
template objects, we may represent them instead with 10 template "batches",
each with template.size=5. Last template object may have a smaller size if
number // batches has a leftover remainder. Template birth dates will not
be on start_dt or end_dt.
start_dt -- datetime.date
end_dt -- datetime.date
template -- object with Life attribute, ie a BusinessUnit
start_num -- int, number of existing templates. 1st name = start_num + 1
number -- integer, number of templates objects to be represented
batches -- integer, number of templates to make, including leftovers
"""
if number < batches:
c = "Number cannot be < Batches!"
raise bb_exceptions.BBAnalyticalError(c)
if number % batches == 0: # Same unit count for every batch
batch_size = number / batches
else:
leftover = number % (batches - 1) # Last batch will have leftovers
if leftover > 0: # Can also change 0 to min_batch_size
batch_size = (number - leftover) / (batches - 1)
else:
batch_size = (number // batches) + 1
# Need this use case when number=100 batches=6. 100/(6-1) = 20
time_diff = end_dt - start_dt # timedelta object
time_interval = time_diff / batches
birth_index = start_dt + time_interval/2 # Start in middle of time period
population = []
unit_count = 1
for i in range(batches):
start_label = start_num + unit_count
unit_count += int(batch_size)
end_label = min(start_num + unit_count - 1, start_num + number)
copy = template.copy()
new_name = copy.name + " " + str(start_label) + "-" + str(end_label)
copy.set_name(new_name)
copy.size = end_label - start_label + 1
# Sets events: conception, birth, death, maturity, old_age
copy.life.configure_events(birth_index)
birth_index += time_interval
population.append(copy)
# print(copy.name, "StoreSize: ",
# copy.size, " Birth: ",
# copy.life.events['birth'])
return population
def _populate_old_actuals(old_model, new_model):
"""
_populate_old_actuals() -> None
--``old_model`` old instance of Engine Model
--``new_model`` new instance of Engine Model
Function looks for hardcoded actuals in old model and copies thier value
onto the the new model.
"""
old_bu = old_model.get_company()
new_bu = new_model.get_company()
old_actl_tl = old_model.get_timeline(resolution='monthly', name='actual')
new_actl_tl = new_model.get_timeline(resolution='monthly', name='actual')
new_proj_tl = new_model.get_timeline(resolution='monthly', name='default')
for old_pd in old_actl_tl.iter_ordered():
# If there are actuals, we want to populate both actl and proj timelines
for tl in [new_actl_tl, new_proj_tl]:
new_pd = tl.find_period(old_pd.end)
if not new_pd:
# new_pd = old_pd.copy()
new_pd = TimePeriod(start_date=old_pd.start,
end_date=old_pd.end,
model=new_model)
tl.add_period(new_pd)
old_fins = old_bu.get_financials(old_pd)
new_fins = new_bu.get_financials(new_pd)
for old_stmt in old_fins.full_ordered:
if not old_stmt:
# Valuation is empty
continue
stmt_name = old_stmt.name.casefold().strip()
new_stmt = new_fins.get_statement(stmt_name)
if not new_stmt:
c = 'Old %s doesn exist!' % old_stmt.name
raise bb_exceptions.BBAnalyticalError(c)
for old_line in old_stmt.get_full_ordered():
old_parent = old_line.relationships.parent
if isinstance(old_parent, LineItem):
new_line = new_stmt.find_first(old_parent.name,
old_line.name)
else:
new_line = new_stmt.find_first(old_line.name)
if old_line.hardcoded:
new_line.set_value(old_line.value,
signature='old hardcoded value',
override=True)
new_line.set_hardcoded(True)
def add_line_summary(self, source_line, target_line, label=None):
"""
SummaryMaker.add_line_summary() -> None
--``source_line`` is the LineItem in a specific time period
--``target_line`` is the LineItem in the summary statement added to
--``label`` is the string label to apply to the summary line in Excel
Method summarizes a LineItem. Aggregation type is defined by
source_line's attributes.
"""
summary_type = target_line.summary_type
if target_line._details:
for new_line in target_line._details.values():
old_line = source_line.find_first(new_line.name)
self.add_line_summary(old_line, new_line, label=label)
elif summary_type in ('derive', 'skip'):
# driver will do the work if 'derive'
pass
elif summary_type in ('sum', 'average'):
target_line.increment(source_line,
consolidating=True,
xl_label=label,
override=True,
over_time=True)
elif summary_type == 'first':
# use the first non-None showing up
if target_line.value is None:
target_line.set_value(source_line.value,
"SummaryMaker",
override=False)
target_line.xl_data.set_ref_source(source_line)
elif summary_type == 'last':
# assume period show up in order, last one overrides
target_line.set_value(source_line.value,
"SummaryMaker",
override=True)
target_line.xl_data.set_ref_source(source_line)
elif summary_type:
c = 'we have not yet implemented summary_type: {}'.format(
summary_type)
raise bb_exceptions.BBAnalyticalError(c)
else:
# this is equivalent to 'last'
target_line.set_value(source_line.value,
"SummaryMaker",
override=True)
target_line.xl_data.set_ref_source(source_line)
def _combine_fins_structure(old_model, new_model):
"""
_build_fins_from_sheet(financials, sheet) -> None
--``old_model`` old instance of Engine Model
--``new_model`` new instance of Engine Model
Function combines line structure from the old and new models
"""
old_bu = old_model.get_company()
new_bu = new_model.get_company()
old_fins = old_bu.financials
new_fins = new_bu.financials
for old_stmt in old_fins.full_ordered:
if not old_stmt:
# Valuation is empty
continue
stmt_name = old_stmt.name.casefold().strip()
new_stmt = new_fins.get_statement(stmt_name)
if not new_stmt:
c = 'Old %s doesn exist!' % old_stmt.name
raise bb_exceptions.BBAnalyticalError(c)
last_position = 0
for old_line in old_stmt.get_full_ordered():
old_parent = old_line.relationships.parent # Can be stmt or line
if isinstance(old_parent, LineItem):
new_line = new_stmt.find_first(old_parent.name, old_line.name)
else:
new_line = new_stmt.find_first(old_line.name)
if not new_line:
new_line = old_line.copy()
if isinstance(old_parent, LineItem):
new_parent = new_stmt.find_first(old_parent.name)
new_parent.add_line(new_line, last_position + 1)
else:
new_stmt.add_line(new_line, last_position + 1)
last_position = new_line.position
def make_closed_units(start_dt, end_dt, template, number, birth_dt=None):
"""
make_closed_units() -> List
Creates a fixed number of units with the same birth date and then closes
them linearly over the dates given.
start_dt -- datetime.date
end_dt -- datetime.date
template -- object with Life attribute, ie a BusinessUnit
number -- integer, number of templates to be created
birth_dt -- datetime.date, birth date for all units (default = start_dt)
All units are assumed to have the same birth date for simplicity.
Unless specified, birth_dt = start_dt
"""
population = []
if not birth_dt:
birth_dt = start_dt
elif birth_dt > start_dt:
c = "Warning Birth Date > Start Date!"
raise bb_exceptions.BBAnalyticalError(c)
time_diff = end_dt - start_dt # timedelta object
time_interval = time_diff / number
date_index = start_dt + time_interval/2 # Start in middle of time period
for i in range(number):
copy = template.copy()
copy.set_name(copy.tags.name + " " + str(i+1))
copy.life.configure_events(birth_dt)
# Sets events: conception, birth, death, maturity, old_age
copy.kill(date_index)
date_index += time_interval
population.append(copy)
return population
def set_value(self, value, signature, override=False):
"""
Line.set_value() -> None
Set line value, add entry to log. Value must be numeric.
Will throw BBPermissionError if line is hardcoded.
If ``override`` is True, skip type check for value and hardcoded
control.
"""
if not override:
try:
test = value + 1
except TypeError:
print(self)
c = "Cannot perform arithmetic on LineItem! override=False"
raise bb_exceptions.BBAnalyticalError(c)
# Will throw exception if value doesn't support arithmetic
if self.hardcoded:
return # Do Nothing if line is hardcoded
new_value = value
if new_value is None:
self._local_value = new_value
else:
if self._details and self.sum_details:
print(self)
m = "Cannot assign new value to a line with existing details."
raise bb_exceptions.BBPermissionError(m)
else:
self._local_value = value
log_entry = (signature, time.time(), value)
self.log.append(log_entry)
self.update_stored_value()
def add_snapshot(self, new_snapshot, overwrite=False):
"""
CapTable.add_snapshot() -> None
--``new_snapshot`` is an instance of Snapshot
--``overwrite`` is False by default
Method adds a dict entry with key=snapshot_date, value=Snapshot
If overwrite is False, method checks that the snapshot does not conflict
with existing entries before adding the new Snapshot.
"""
key = new_snapshot.ref_date
# Make sure this snapshot is valid
if not overwrite:
if key in self.snapshots:
c = "Cannot overwrite existing snapshot. %s already" \
" exists in directory." % key
raise bb_exceptions.BBAnalyticalError(c)
self.snapshots[key] = new_snapshot
def add_round(self, new_round, overwrite=False):
"""
CapTable.add_record() -> None
--``new_round`` is an instance of Round
--``overwrite`` is False by default
Method adds a dict entry with key=round_name, value=Round
If overwrite is False, method checks that the record does not conflict
with existing entries before adding the new Round.
"""
key = new_round.name
# Make sure this round is valid
if not overwrite:
if key in self.rounds:
c = "Cannot overwrite existing round. %s already" \
" exists in directory." % key
raise bb_exceptions.BBAnalyticalError(c)
self.rounds[key] = new_round
def add_record(self, record, overwrite=False):
"""
Snapshot.add_record() -> None
--``record`` is an instance of record
--``overwrite`` is False by default
Method adds a dict entry with key=(owner_name, round_name), value=Record
If overwrite is False, method checks that the record does not conflict
with existing entries before adding the new record.
"""
key = (record.owner_name, record.round_name)
# Make sure this record is valid
if not overwrite:
if key in self.records:
c = "Cannot overwrite existing record. (%s, %s) already" \
" exists in directory." % key
raise bb_exceptions.BBAnalyticalError(c)
self.records[key] = record
def find_most_recent(query_date, date_pool):
"""
find_most_recent(query_date, date_pool) -> datetime.date
Function returns the date in pool that most closely precedes query date. If
query date is itself in pool, function will return query_date.
``date_pool`` should be a container of sortable objects
Selection algorithm sorts a set of date_pool contents together with
query_date and returns the value that precedes query_date.
Function raises an error if query_date is earlier than every item in pool.
"""
last_known = None
#
if not isinstance(query_date, date):
query_date = for_parsing.date_from_iso(query_date)
#if query is not a valid date, try to convert it
#
if query_date in date_pool:
last_known = query_date
else:
w_query = set(date_pool) | {query_date}
w_query = sorted(w_query)
i_query = w_query.index(query_date)
if i_query == 0:
c = "Query is earliest date in pool."
raise bb_exceptions.BBAnalyticalError(c, query_date)
else:
i_prior = i_query - 1
last_known = w_query[i_prior]
#
return last_known
def check_engine_message(engine_message):
"""
engine_message_status() -> str
Function compares the message to known patterns stored in message_patterns
dictionary and returns fit. If message contains the END_INTERVIEW
sentinel in last position (ie, message is in xxEND formaT), function
returns endSession status without running further logic.
"""
if engine_message[2] == END_INTERVIEW or engine_message[2] == USER_STOP:
status = message_patterns[p_end_session]
else:
pattern = (int(bool(x)) for x in engine_message)
# tuple for use as dict key
pattern = tuple(pattern)
try:
status = message_patterns[pattern]
except KeyError:
label = "Unusual message format"
raise bb_exceptions.BBAnalyticalError(label)
# something odd happening here
return status
def _set_behavior(behavior_str, limits_str, statement, line, model):
"""
_set_behavior(sheet, cell_f, bu) -> bool
--``behavior_str`` is string representing a JSON dict
--``limits_str`` is string representing of a list of 2 item lists
--``line`` is the instance of LineItem which we want to add behavior
--``model`` is an instance of EngineModel
Function takes a behavior and and limits string and figures out a formula
and driver to attach to the line.
"""
try:
behavior_dict = json.loads(behavior_str)
except ValueError:
c = "Invalid JSON String: " + behavior_str
raise bb_exceptions.BBAnalyticalError(c)
line.usage.behavior = behavior_dict
if not limits_str:
# set default value
limits_str = '[[1.3,"Overperforming"],[1.1,"Performing"]]'
try:
limits_list = json.loads(limits_str)
except ValueError:
c = "Invalid JSON String: " + limits_str
raise bb_exceptions.BBAnalyticalError(c)
line.usage.limits = limits_list
if behavior_dict.get('action'):
action_name = behavior_dict.pop('action')
else:
action_name = "rolling sum over time"
if action_name.casefold().strip() == "custom status":
statement.display_type = statement.COVENANT_TYPE
formula_name = ps.ACTION_TO_FORMULA_MAP.get(action_name)
if not formula_name:
c = "No formula mapped to the action name of: " + action_name
raise bb_exceptions.BBAnalyticalError(c)
f_id = FC.by_name[formula_name]
formula = FC.issue(f_id)
if not formula:
c = "No formula found by name of: " + formula_name
raise bb_exceptions.BBAnalyticalError(c)
required_keys = formula.required_data
if len(required_keys - behavior_dict.keys()) == 0:
if not line.get_driver():
data = dict()
data.update(behavior_dict) # Same key names
parent_name = line.relationships.parent.name
dr_name = (parent_name or "") + ">" + line.name
driver = model.drivers.get_or_create(dr_name, data, formula)
line.assign_driver(driver.id.bbid)
def _build_fins_from_sheet(bu, sheet, sm):
"""
_build_fins_from_sheet(financials, sheet) -> None
--``bu`` is the instance of Business Unit we want build Financials on
--``sheet`` is an instance of openpyxl.WorkSheet
--``sm`` is an instance of SheetMap
Function extracts information from 'sheet' to build the LineItem structure
in 'financials'. Functions creates new statements if necessary.
"""
full_order = list()
financials = bu.financials # SSOT Fins
line = None
# Loop through each row that contains LineItem information
# Start at the first row that has line data
iter_rows = sheet.iter_rows(row_offset=sm.rows["FIRST_DATA"]-1,
column_offset=0)
for row in iter_rows:
if not row[sm.cols[ps.STATEMENT]-1].value:
if line:
line.xl_format.blank_row_after = True
# Skip blank rows
continue
statement_name = row[sm.cols[ps.STATEMENT]-1].value.strip()
if "parameter" in statement_name.casefold():
continue # Ignore parameters
# put statements in order
if statement_name.casefold() not in full_order:
full_order.append(statement_name.casefold())
statement = financials.get_statement(statement_name)
if not statement and statement_name:
statement = Statement(statement_name)
financials.add_statement(name=statement_name, statement=statement)
if statement is financials.valuation:
statement.compute = True
line_name = row[sm.cols[ps.LINE_NAME]-1].value
if not line_name:
continue # Must have line name
line_title = row[sm.cols[ps.LINE_TITLE]-1].value
if not line_title:
continue # Must have line title
parent_name = row[sm.cols[ps.PARENT_NAME]-1].value or ""
# find_first always needs a str.
num_parents = len(statement.find_all(parent_name))
if num_parents > 1:
c = "PARENT_NAME must be unique within a statement!"
c += " There are %s occurances of %s" % (num_parents, parent_name)
# Current design flaw is that if there are more than one
# parent lines with the same name, we do not know which one to use
raise bb_exceptions.BBAnalyticalError(c)
parent = statement.find_first(parent_name)
if not parent:
if parent_name:
# Add parent as a top level line, can be moved later
parent = LineItem(parent_name)
statement.append(parent)
# Main logic below
existing_line = statement.find_first(line_name)
# existing_line could be the line we want or another line with the same
# name but under a different parent
if not existing_line:
line = LineItem(line_name)
if parent:
parent.append(line)
else:
statement.append(line)
else:
# A line with the same name exists on statement
# We want to check if in the right location
if parent:
if statement.find_first(parent_name, line_name):
# Existing_line is the line we want to edit
# It is already under the correct parent
line = existing_line
else:
if existing_line.relationships.parent is statement:
# Existing_line was first created as a parent line,
# Move existing_line to the correct parent
# Existing_line is the line we want to edit
line = statement.find_first(line_name, remove=True)
parent.append(line)
else:
# Existing_line is NOT the line we want to edit
# Create a new line, same name, different parent
line = LineItem(line_name)
parent.append(line)
else:
# Existing_line is the line we want to edit
# Its location is correct at the top level
line = existing_line
if not line:
c = "Import Error: Line %s, Parent %s" % (line_name, parent_name)
c += ". Check financials structure."
raise bb_exceptions.BBAnalyticalError(c)
line.set_title(line_title)
_add_line_effects(line, bu, row, sm)
financials.set_order(full_order)
print(financials)
def revise_projections(xl_serial, old_model):
"""
revise_projections(xl_serial, engine_model) -> EngineModel()
--``xl_serial`` serialized string of an Excel workbook (".xlsx")
--``old_model`` old instance of Engine Model
Function revises projection values while keeping existing actuals values
Function takes a serialized Excel workbook in a specific format
and converts it to an EngineModel with LineItem values.
Function delegates to:
build_sheet_maps()
_build_fins_from_sheet()
_populate_fins_from_sheet()
"""
new_model = Model(bb_settings.DEFAULT_MODEL_NAME)
new_model.start()
new_model._ref_date = old_model._ref_date
# 1) Extract xl_serial. Make sure it is in the right format
wb = xlio.load_workbook(xl_serial, data_only=True) # Includes Values Only
wb_f = xlio.load_workbook(xl_serial, data_only=False) # Includes Formulas
# For testing local excel files:
# filename = r"C:\Workbooks\Forecast_Rimini8.xlsx"
# wb = xlio.load_workbook(filename=filename, data_only=True)
# Look for the BB Metadata tab
metadata_names = ps.BB_METADATA_NAMES
bb_tabname = None
for tab in wb:
if tab.title.casefold() in metadata_names:
bb_tabname = tab.title
break
if not bb_tabname:
c = "No BB Metadata Tab!"
raise bb_exceptions.BBAnalyticalError(c)
sheet = wb[bb_tabname]
sheet_f = wb_f[bb_tabname]
# Make sure sheet is valid format
sm = build_sheet_map(sheet)
company = new_model.get_company()
if not company:
company = BusinessUnit(new_model.name)
new_model.set_company(company)
new_model.target = company
# 2) Determine Line Structure from Excel upload
_build_fins_from_sheet(company, sheet, sm)
# 3) Add any missing lines from old model
_combine_fins_structure(old_model, new_model)
# 4) Make sure actuals timeline has same structure
actl_tl = new_model.get_timeline('monthly', name='actual')
if not actl_tl:
actl_tl = TimeLine(new_model)
new_model.set_timeline(actl_tl, resolution='monthly', name='actual')
# 5) Write values to both actuals and projected.
_populate_fins_from_sheet(new_model, sheet, sheet_f, sm)
# 6) Retain any actual values from old model that are hardcoded
_populate_old_actuals(old_model, new_model)
return new_model
def _add_line_effects(line, bu, row, sm):
"""
_add_line_effects(financials, sheet) -> None
--``line`` is the target LineItem
--``bu`` is the instance of Business Unit that the line belongs to
--``row`` is list of openpyxl.Cells in the same row
--``sm`` is an instance of SheetMap
Function validates column values. This information and stores on the line
so that the Engine knows what to do with line later.
"""
# Add comparison ("<" or ">") as a tag for KPI and Covenant analysis
if sm.cols[ps.COMPARISON]:
comparison_str = row[sm.cols[ps.COMPARISON]-1].value
if comparison_str in ('<', '<=', '>', '>='):
# Only tag valid comparisons
line.tags.add(comparison_str)
# Add sum_details attribute if FALSE (TRUE is default for blank cells)
if sm.cols[ps.SUM_DETAILS]:
sum_details = row[sm.cols[ps.SUM_DETAILS]-1].value
if not (_check_truthy(sum_details) or sum_details is None):
line.sum_details = False
# Tag line with which summary report we want to display it on.
if sm.cols[ps.REPORT]:
report_str = row[sm.cols[ps.REPORT]-1].value
if _check_truthy(report_str, others=ps.VALID_REPORTS):
line.usage.show_on_report = True
if sm.cols[ps.MONITOR]:
monitor_bool = row[sm.cols[ps.MONITOR]-1].value
if _check_truthy(monitor_bool):
line.usage.monitor = True
if sm.cols[ps.PARSE_FORMULA]:
parse_formula_bool = row[sm.cols[ps.PARSE_FORMULA]-1].value
if _check_truthy(parse_formula_bool):
line.tags.add('parse formula')
if sm.cols[ps.ADD_TO_PATH[0]]:
topic_formula_bool = row[sm.cols[ps.ADD_TO_PATH[0]] - 1].value
if _check_truthy(topic_formula_bool):
new_path_line = LineItem(line.name)
bu.stage.path.append(new_path_line)
line.tags.add('topic formula')
if sm.cols[ps.ALERT[0]]:
alert_val = row[sm.cols[ps.ALERT[0]] - 1].value
if alert_val is not None:
new_path_line = LineItem(line.name + " alert")
new_path_line.tags.add('alert commentary')
bu.stage.path.append(new_path_line)
line.tags.add('alert commentary')
# Backwards compatibility when ALERT was a bool column
if _check_truthy(alert_val):
alert_val = '{"comparison":"=","limit":"Needs Review"}'
try:
conditions_dict = json.loads(alert_val)
line.usage.alert_commentary = conditions_dict
except ValueError:
c = "Invalid JSON String: " + alert_val
raise bb_exceptions.BBAnalyticalError(c)
if sm.cols[ps.ON_CARD]:
on_card_bool = row[sm.cols[ps.ON_CARD] - 1].value
if _check_truthy(on_card_bool):
line.usage.show_on_card = True
# Tag line with one or more tags.
if sm.cols[ps.TAGS]:
tags_str = row[sm.cols[ps.TAGS]-1].value
if tags_str:
tags_list = tags_str.split(",")
for t in tags_list:
new_tag = t.strip() # Remove white space from both sides
line.tags.add(new_tag)
def add_projections(xl_serial, engine_model):
"""
add_projections(xl_serial, engine_model) -> EngineModel()
--``xl_serial`` serialized string of an Excel workbook (".xlsx")
--``engine_model`` instance of Engine Model
Function takes a serialized Excel workbook in a specific format
and converts it to an EngineModel with LineItem values. Model input will
determine which investment card we are adding projections to.
Function delegates to:
build_sheet_map()
_build_fins_from_sheet()
_populate_fins_from_sheet()
"""
model = engine_model
# 1) Extract xl_serial. Make sure it is in the right format
wb = xlio.load_workbook(xl_serial, data_only=True) # Includes Values Only
wb_f = xlio.load_workbook(xl_serial, data_only=False) # Includes Formulas
# For testing local excel files:
# filename = r"C:\Workbooks\Forecast_Rimini8.xlsx"
# wb = xlio.load_workbook(filename=filename, data_only=True)
# Look for the BB Metadata tab
metadata_names = ps.BB_METADATA_NAMES
bb_tabname = None
for tab in wb:
if tab.title.casefold() in metadata_names:
bb_tabname = tab.title
break
if not bb_tabname:
c = "No BB Metadata Tab!"
raise bb_exceptions.BBAnalyticalError(c)
sheet = wb[bb_tabname]
sheet_f = wb_f[bb_tabname]
# Make sure sheet is valid format
sm = build_sheet_map(sheet)
# 2) Align model.time_line to ref_date. Add additional periods as needed
sheet_rows = [r for r in sheet.rows]
header_row = sheet_rows[sm.rows["HEADER"]-1]
xl_dates = []
for cell in header_row[sm.cols["FIRST_PERIOD"]-1:]:
if isinstance(cell.value, datetime):
xl_dates.append(cell.value.date())
first_date = xl_dates[0]
if isinstance(first_date, str):
first_date = datetime.strptime(first_date, "%Y-%m-%d").date()
# set default timeline to only contain relevant dates
new_timeline = TimeLine(model)
model.set_timeline(new_timeline, overwrite=True)
model.time_line.build(first_date, fwd=0, year_end=False)
model.change_ref_date(first_date)
company = model.get_company()
if not company:
company = BusinessUnit(model.name)
model.set_company(company)
model.target = company
# 3) Determine Line Structure from Excel upload
_build_fins_from_sheet(company, sheet, sm)
# 4) Make sure actuals timeline has same structure
actl_tl = model.get_timeline('monthly', name='actual')
if not actl_tl:
actl_tl = TimeLine(model)
model.set_timeline(actl_tl, resolution='monthly', name='actual')
# 5) Write values to both actuals and projected.
_populate_fins_from_sheet(model, sheet, sheet_f, sm)
return model
def _populate_fins_from_sheet(engine_model, sheet, sheet_f, sm):
"""
_populate_fins_from_sheet() -> None
--``engine_model`` is the instance of EngineModel
--``sheet`` is an instance of openpyxl.WorkSheet with Values
--``sheet_f`` is an instance of openpyxl.WorkSheet with Formulas as str
--``sm`` is an instance of SheetMap
Function extracts LineItem values from 'sheet' and writes them to
'financials' for multiple periods. Function assumes that the structure
for financials is already in place for every period.
"""
model = engine_model
bu = model.get_company()
proj_tl = model.get_timeline('monthly', name='default')
actl_tl = model.get_timeline('monthly', name='actual')
ssot_fins = bu.financials
# Loop across periods (Left to Right on Excel)
sheet_columns = [c for c in sheet.columns]
for col in sheet_columns[sm.cols["FIRST_PERIOD"]-1:]:
dt = col[0].value.date()
timeline_name = col[sm.rows["TIMELINE"]-1].value
start_dt = date(dt.year, dt.month, 1)
end_dt = date(dt.year, dt.month, 28)
while end_dt.month == start_dt.month:
end_dt += timedelta(1)
end_dt -= timedelta(1)
# Always add a proj_pd regardless of if something is forecast or actual
proj_pd = proj_tl.find_period(dt)
if not proj_pd:
proj_pd = TimePeriod(start_date=start_dt,
end_date=end_dt,
model=model)
proj_tl.add_period(proj_pd)
actl_pd = actl_tl.find_period(dt)
if _check_truthy(timeline_name, others=["actual"]):
if not actl_pd:
actl_pd = TimePeriod(start_date=start_dt,
end_date=end_dt,
model=model)
actl_tl.add_period(actl_pd)
# Loop across Lines (Top to Down on Excel)
for cell in col[sm.rows["FIRST_DATA"]-1:]:
# Skip blank cells
if cell.value in (None, ""):
behavior_str = ''
if sm.cols[ps.BEHAVIOR]:
behavior_str = sheet.cell(row=cell.row,
column=sm.cols[ps.BEHAVIOR]).value
if not behavior_str:
continue
row_num = cell.row
col_num = cell.col_idx
statement_str = sheet.cell(row=row_num, column=sm.cols[ps.STATEMENT]).value
if not statement_str:
continue
statement_name = statement_str.casefold().strip()
line_name = sheet.cell(row=row_num, column=sm.cols[ps.LINE_NAME]).value
parent_name = sheet.cell(row=row_num, column=sm.cols[ps.PARENT_NAME]).value
# Handle rows where we just want to add a parameter value
if statement_name in ("parameters", "parameter"):
if parent_name in ("Timeline", "timeline"):
if cell.value is not None:
actl_tl.parameters.add({line_name: cell.value})
proj_tl.parameters.add({line_name: cell.value})
else:
if actl_pd:
actl_pd.parameters.add({line_name: cell.value})
if proj_pd:
proj_pd.parameters.add({line_name: cell.value})
# # print("Param", cell.value)
continue
# # print(statement_name, line_name, parent_name)
if actl_pd:
actl_fins = bu.get_financials(actl_pd)
actl_stmt = actl_fins.get_statement(statement_name)
proj_fins = bu.get_financials(proj_pd)
proj_stmt = proj_fins.get_statement(statement_name)
# Always match number formats
ssot_stmt = ssot_fins.get_statement(statement_name)
ssot_line = ssot_stmt.find_first(line_name)
if cell.number_format and not ssot_line.xl_format.number_format:
ssot_line.xl_format.number_format = cell.number_format
# Status column
if sm.cols[ps.STATUS]:
status_cell = sheet.cell(row=row_num, column=sm.cols[ps.STATUS])
status_str = status_cell.value
if status_str:
try:
status_dict = json.loads(status_str)
except ValueError:
c = "Invalid JSON String: " + status_str
raise bb_exceptions.BBAnalyticalError(c)
required_keys = {"style"}
missing_keys = required_keys - status_dict.keys()
if len(missing_keys) > 0:
c = "Missing Keys: " + missing_keys
raise bb_exceptions.BBAnalyticalError(c)
# If a key is "0.8", turn it into 0.8
for key in status_dict:
try:
float(key)
except ValueError:
continue
else:
status_value = status_dict.pop(key)
status_dict[float(key)] = status_value
ssot_line.usage.status_rules = status_dict
if ssot_line.usage.status_rules and ssot_stmt.display_type == \
ssot_stmt.REGULAR_TYPE:
ssot_stmt.display_type = ssot_stmt.KPI_TYPE
# Behaviour column
if sm.cols[ps.BEHAVIOR]:
behavior_cell = sheet.cell(row=row_num,
column=sm.cols[ps.BEHAVIOR])
behavior_str = behavior_cell.value
limits_str = None
if sm.cols[ps.LIMITS]:
limits_cell = sheet.cell(row=row_num,
column=sm.cols[ps.LIMITS])
limits_str = limits_cell.value
if behavior_str:
_set_behavior(behavior_str, limits_str, ssot_stmt,
ssot_line, model)
continue # Don't parse or hardcode line if behavior exists
# Look to see if Formula should be automatically imported
cell_f = sheet_f.cell(row=row_num, column=col_num)
can_parse = _parse_formula(sheet, cell_f, bu, sm)
# Otherwise, set the lowest level lines to a hardcoded value
if can_parse is False:
# Always populate projected statement. (Include Historical Actuals)
_populate_line_from_cell(cell, line_name, parent_name, proj_stmt)
if _check_truthy(timeline_name, others=["actual"]):
_populate_line_from_cell(cell, line_name, parent_name, actl_stmt)
if ssot_line.usage.status_rules and ssot_stmt.display_type == \
ssot_stmt.REGULAR_TYPE:
ssot_stmt.display_type = ssot_stmt.KPI_TYPE
def get_units_linearly(number, container):
"""
get_units_linearly() -> list
From container, function picks a fixed number of objects which are evenly
distributed by age ranking. Returns list of objects ordered by birth date.
number -- integer, number of objects to be returned
container -- iterable, containing objects with life attribute
Returned list has pointers to the original existing objects
It does NOT return copies of these objects.
Function caller should decide what goes into container. Can include dead
units, alive units, or gestating + alive units. Caller should also decide
what date filters to use for the container objects.
"""
# Error Checking
if number > len(container):
c = 'Error: Number > Container Length %s' % len(container)
raise bb_exceptions.BBAnalyticalError(c)
# Populate master_list, a sorted list of container objects by birth date
master_list = list()
if type(container) == dict:
master_list = list(container.values())
else:
# if container is a list or set
master_list = list(container.copy())
#
master_list.sort(key=lambda x: x.life.events[KEY_BIRTH])
len_master = len(master_list)
population = []
if number == len_master:
population = master_list
# Save all the time if we want all of the units anyways
elif number == 0:
population = []
elif number <= len_master/2:
# Append units from master list if choosing less than 1/2 of them
index_interval = len_master/number
# Place the end units, in the middle of period instead of at start date
index = 0 + math.floor(index_interval/2)
for i in range(number):
target_bu = master_list[index]
population.append(target_bu)
units_needed = number - i - 1
if units_needed:
len_remaining = len_master - index - index_interval/2
# index_interval/2 also leaves a buffer at the end if necessary
index_interval = len_remaining/units_needed
index += round(index_interval)
else:
# Remove unwanted units from master list if picking > 1/2 of them
unwanted_number = len_master - number # choose number of unwanted units
index_interval = len_master/unwanted_number
# Place the end units, in the middle of period instead of at start date
index = 0 + math.floor(index_interval/2)
for i in range(unwanted_number):
master_list.pop(index-i)
# must do index-i since getting master list is losing length
units_needed = unwanted_number - i - 1
if units_needed:
len_remaining = len_master - index - index_interval/2
# index_interval/2 also leaves a buffer at the end if necessary
index_interval = len_remaining/units_needed
index += round(index_interval)
# Take the remaining units that haven't been removed from master_list
population = master_list
return population
def grow_batches_by_count(start_dt, end_dt, template, start_num, number, batches):
"""
grow_batches_by_count() -> List
Returns list of a fixed number of template objects with constant geometric
growth rate over time. Function is designed to be used when the number > 50.
Instead of creating 50 objects, we may create 10 objects each with size=5
The last template may have a smaller size than the rest if number // batches
has leftovers.
start_dt -- datetime.date
end_dt -- datetime.date
template -- object with Life attribute, ie a BusinessUnit
start_num -- int, number of starting BUs of this same type
number -- integer, number of sum of the template.size for every batch
batches -- integer, number of templates to make, including rump
The total number of objects created must be known, so this function is
useful for populating historical time periods and near term forecasts.
Generally if start_num is low (<5 units) it is better to use use
make_linear_pop instead.
Start_dt should be when the last already existing unit has been created.
Function will not make a unit on start_date
Function will always make the last new unit on end_dt.
"""
if number < batches:
c = "Number cannot be < Batches!"
raise bb_exceptions.BBAnalyticalError(c)
if number % batches == 0: # Same unit count for every batch
batch_size = number / batches
else:
leftover = number % (batches - 1) # Last batch will have leftovers
if leftover > 0: # Can also change 0 to min_batch_size
batch_size = (number - leftover) / (batches - 1)
else:
batch_size = (number // batches) + 1
# Need this use case when number=100 batches=6. 100/(6-1) = 20
start_batches = (start_num or 1) / batch_size
# Existing number of batches. Cannot have 0 value
population = []
time_diff = end_dt - start_dt # timedelta object
time_diff_years = time_diff.days / 365
rate = (1/time_diff_years) * math.log(batches/start_batches + 1) # Eq: A
birth_dates = []
for K in range(batches):
t = (1/rate) * math.log((K+1)/start_batches + 1) # Eq: B
t_timedelta = timedelta(t * 365)
birth_dates.append(t_timedelta + start_dt)
unit_count = 1
for birthday in birth_dates:
start_label = start_num + unit_count
unit_count += int(batch_size)
end_label = min(start_num + unit_count - 1, start_num + number)
copy = template.copy()
# Name of 1st template created will be labeled (start_num + 1)
new_name = copy.name + " " + str(start_label) + "-" + str(end_label)
copy.set_name(new_name)
copy.size = end_label - start_label + 1 # "BU 101-102" is 2 units
# Sets events: conception, birth, death, maturity, old_age
copy.life.configure_events(birthday)
population.append(copy)
# print(copy.name, "StoreSize: ",
# copy.size, " Birth: ",
# copy.life.events['birth'])
return population