model_Excel module
Created on Fri Feb 12 07:04:02 2016
@author: ibh
Takes all formula’s from a excel work book and translates each to the equivalent expression. Openpyxl is the fastest library but it can not deal all values. Therefor xlwings is also used. But only to read repeated formula’s which inly will show as ‘=’
Also defines function used when using xlwings to automate excel.
These are used in modeldump_excel and modelload_excel
Some of the docstring are not very informative, to be improved.
- model_Excel.findequations(name)[source]
Takes all formula’s from a excel work book and translates each to the equivalent expression.
Multicell ranges are expanded to a comma separated list.
The ordinary operators and the SUM function can be handled. If you need more functions. You have to impelent them in the modelclass.
In the model each cell reference is prefixed by <sheet name>_
Openpyxl is the fastest library and it has a tokenizer but it can not read all values.
Therefor xlwings is used to read repeated formula’s which Openpyxl will show as ‘=’
- input:
- name:
Location of a excel sheeet
- Returns:
- modeldic:
A dictionary with formulars keyed by cell reference
- model_Excel.findvalues(name)[source]
Finds numerical values in a excel workbook with a value different from 0
- model_Excel.findcoordinates(name)[source]
Finds the cell references matching the codes in a LCR workbook from EBA
This is needed for the mapping of the raw data to the excel cell refereces.
- input:
- name:
Location of a excel sheeet
- Returns:
coldf: Dataframe with mapping between excel column and EBA columns_code :rowdf: Dataframe with row with mapping between excel row and EBS data row_code
- model_Excel.getexcelmodel(name)[source]
Creates a model instance from a excel sheet SUM is replaced by SUM_EXCEL which is a function in the modelclass
In the excel formulars this function accepts ordinary operators and SUM in excel sheets
- input:
- name:
Location of a excel sheeet
- Returns:
model: A model instance with the formulars of the excel sheet :para: A list of values in the sheet which matches exogeneous variables in the model
- model_Excel.df_to_sheet(name, df, wb, after=None)[source]
Dataframe to sheet
- Parameters:
name (TYPE) – DESCRIPTION.
df (TYPE) – DESCRIPTION.
wb (TYPE) – DESCRIPTION.
after (TYPE, optional) – DESCRIPTION. Defaults to None.
- Returns:
DESCRIPTION.
- Return type:
sht (TYPE)
- model_Excel.obj_to_sheet(name, obj, wb, after=None)[source]
An python object to sheet
- Parameters:
name (TYPE) – DESCRIPTION.
obj (TYPE) – DESCRIPTION.
wb (TYPE) – DESCRIPTION.
after (TYPE, optional) – DESCRIPTION. Defaults to None.
- Returns:
None.