pyfpa package

Listing of functions for pyfpa

Submodules

pyfpa.pyfpa module

Module contents

Project for Financial Planning and Analysis

Written by Erik Warren Original Date: October 2020 Version Date: November 2020 version: 0.0.7 beta

class pyfpa.fpa(df=Empty DataFrame Columns: [] Index: [])

Bases: object

add_block_to_data()

Takes the block data object, arranges the index and adds it to the data object. Even if the indexes don’t match, this will fill in the missing pieces.

add_dimensions(new_dimensions, dim_values_to_add, col_num=1, data_obj='data')

Append a dimension to the index with a new name, values and where to place it.

Parameters

• new_dimensions – Name of the new dimension or dimensions. String value or list i.e. ‘Department’ or [‘Department’, ‘Region’]

• dim_values_to_add – Values of the new dimension or dimensions. String value or list i.e. ‘Sales’ or [‘Sales’, ‘EMEA’]

• col_num – Where in the index to place the new dimension. 1 indicates 1 column from the left.

• data_obj – Which data object you want o effect. Available - ‘block’, ‘slice’, ‘consolidation’, ‘function_result’, ‘data’

Returns

data_obj

column_slice(dims=None, dim_values=None, col_range=None, col_list=None, data_obj='data', append_to=False)

combine_dimensions(combine_order, data_obj='data')

comma_format(decimals=0)

Changes the display options to show commas and decimal places. Choices are 0, 1, 2.

consol_dimension(dims='Data_Block', data_obj='data')

Consolidates a data object on a certain dimension of the data object.

Parameters

• dim – Dimension to consolidate on. I.e. ‘Department’ or ‘SalesPerson’.

• data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

self.consolidation

dim_to_date(dim, data_obj='data')

Attempts to change a dimension or column from object to datetime.

Parameters

• dim – String value of which dimension contains the item you want to change. I.e. ‘Region’

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

drop_dimension(dimension_drop, data_obj='data')

Remove a dimension from the index.

Parameters

• dimension_drop – Dimension to drop. I.e. ‘Department’

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

fpa_help = 'pyfpa - Financial Planning and Analysis Python Project. Python intro for FP&A people.'

get_block_info(db_id)

Retrieve the meta information from a data block. Just plug in the data block number.

Parameters

db_id – Interger of the data block. I.e. x.get_block_info(49593949)

Returns

Meta information for Data_Block.

get_duplicates(based_on=None, data_obj='data')

Retrieves repetitive records based on the index. Can be filtered down by dimensions.

Parameters

• based_on – String or list object of dimensions to be used. If blank it will all dimensions.

• data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘data’

Returns

self.function_result

help_add_dimensions = "Add dimensions and values with lists ['???',...]"

help_import_custom_xl = 'Import Excel using custom mapping for table and dimensions. Enter most items as lists.'

help_import_xl = 'Import Excel, CSV or DataFrame to .block. cols_to_index are columns for index which can be int or list for Excel. For DataFrame, string or list of string column names.'

help_merge_dim_from_accts = 'One-to-One add of a dimension from an accounts based on existing dimension'

help_merge_dim_from_xl = 'One-to-One add of a dimension from an excel list based on existing dimension'

import_accts_xl(f_path, ws_name=0, dim_name='nval', sep_val=',')

Import a dataframe such as a chart of accounts or sales dimensions for adding to data objects.

Parameters

• f_path – Path to Excel file.

• ws_name – Name or index number of the worksheet. i.e. ‘Accounts’ or 2.

• dim_name – Identifier for the group used when retrieving it. See merge_dim_from_accts.

Returns

self.accounts

import_custom_xl(f_path, ws_name=0, table_coords=None, idx_cols=0, dim_names=None, dim_values=None, dim_names_coords=None, dim_coords=None, fill_index_na=False)

Custom import with mapping for data table and dimensions from Excel worksheet.

Parameters

• f_path – Path to the Excel File.

• ws_name – Name or index number of the worksheet. i.e. ‘Accounts’ or 2.

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_values – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

import_xl(fpath, ws_name=0, cols_to_index=0, sep_val=',')

Import a table from a worksheet in a Excel File, a CSV file or an existing pandas DataFrame.

Parameters

• fpath – path to file OR a pandas DataFrame

• ws_name – worksheet name or index such as 0 or 2

• cols_to_index – columns to put into index either a number or a list i.e. [0, 1, 2]

import_xl_directories(dir_path, xl_id=None, ws_name=0, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None, fill_index_na=False)

Imports dimensions and table from a worksheet from all the Excel files (with or without identifiers) from a series of directories.

Parameters

• dir_path – Path to the root directory containing the directories which contain the files.

• xl_id – String used to filter files to extract data, i.e. if file name is “Budget v3.xlsx” you could say ‘v3.xlsx’

• ws_name – Worksheet to read. Input as string i.e. ‘Sales’ or index. Zero is default.

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

Returns

self.data

import_xl_directory(dir_path, xl_id=None, ws_name=0, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None, fill_index_na=False)

Imports dimensions and table from a worksheet from all the Excel files (with or without identifiers) from a directory.

Parameters

• dir_path – Path to the directory containing the files

• xl_id – String used to filter files to extract data, i.e. if file name is “Budget v3.xlsx” you could say ‘v3.xlsx’

• ws_name – Worksheet to read. Input as string i.e. ‘Sales’ or index. Zero is default.

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

Returns

self.data

import_xl_sheets(f_path, wb_sheets=None, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None, fill_index_na=False)

Imports all the tables from worksheets within an Excel file.

Parameters

• f_path – Path to the file

• wb_sheets – Worksheets to read. Input as list i.e. [‘Sales’, ‘Operations’]

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

Returns

self.data

keyword_replace(target_words, replace_words, dims=None, data_obj='data')

Replace a word or words in the index. For example, if you wanted to replace [‘Payroll’, ‘East’] with [‘Salaries & Wages’, ‘Northeast’].

Parameters

• target_words – String or list of word(s) to be replace. I.e. ‘Software’ or [‘Payroll’, ‘East’].

• replace_words – String or list of word(s) to insert. I.e. ‘3rd Party Code’ or [‘Salaries & Wages’, ‘Northeast’].

• dims – String or list of specific dimensions to effect.

• data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘data’, ‘slice’

Returns

data_obj

keyword_slice(keywords, dims=None, data_obj='data', append_to=False)

Slice data based on string fragment. Function will search the index & data and return any rows with the search string.

Parameters

• keywords – String object of which to search. I.e. ‘Rent’, ‘LLC’, ‘Smith’

• dims – String or list object of specific dimensions to search. Blank will search all dimensions & data.

• data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘data’, ‘slice’

• append_to – True indicates new search will be appended to existing search results. False means it won’t.

Returns

self.slice

load_project(path_name)

Imports the project by filling the ‘data’, ‘meta_block’ and ‘accounts’ data objects.

Parameters

path_name – Path to directory for normal save or to the json file. I.e. for json file ‘C:/Budgets/Budgets v1.json’ or ‘C:/Budgets/’ for normal load.

Returns

project

make_pivot_table(value_col, index_names, col_names=None, data_obj='data', function='sum', totals=True, total_names='Total')

make_records(data_obj='data')

Change the data object to a records format rather than a table. Works bes on single level columns.

Parameters

data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

make_records_for_pivot(data_obj='data')

Change the data object to a records format so it can be pasted into Excel in a pivot table friendly format. Only works if the column index is a single level.

Parameters

data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

merge_dim_from_accts(dim_set, base_dim, new_dims, data_obj='data')

Add dimension based on account data object. If you have a chart of accounts you could add the account number to the Line Item if you have it.

Parameters

• dim_set – Section of ‘accounts’ data object to take as new dimensions.

• base_dim – Existing dimension in the index and accouts on which to merge the new dimension

• new_dims – Column from the accounts data object table to add to the dataframe

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’

Returns

data_object

merge_dim_from_xl(fpath, ws_name, base_dim, new_dims, data_obj='data')

Add dimension based on an table from an Excel file. If you have a chart of accounts you could add the account number to the Line Item if you have it.

Parameters

• base_dim – Existing dimension in the index on which to merge the new dimension

• new_dims – Column from the accounts data object table to add to the dataframe

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’

Returns

data_object

move_col_to_dims(dims, data_obj='data')

Move a dimension from the columns in the data to the index.

Parameters

• dims – Dimension to drop. I.e. ‘Department’

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

move_dims_to_col(dims, data_obj='data')

Move a dimension from the index to a column in the data.

Parameters

• dims – Dimension to drop. I.e. ‘Department’

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

multiply_dim(dim_name, dim_vals=None, calc_name='New Item', data_obj='slice')

Multiply two or more dimension items. For example, multiply units x price x discount with x.multiply_dim(‘Basis’, [‘Units’, ‘Price’, ‘Discount’] :param dim_name: String object of dimension name. I.e. ‘Basis’. :param dim_vals: List object of dimension items to multiply. I.e. [‘Units’, ‘Price’, ‘Discount’] :param calc_name: String object of new dimension item name. I.e. ‘Total_Revenue’ :param data_obj: Which data object you want o effect. Default: ‘slice’. Available - ‘data’, ‘slice’ :return: self.function_result

remove_duplicates(based_on=None, keep_item='last', data_obj='data')

Deletes repetitive records based on the index. Can be filtered down by dimensions.

Parameters

• based_on – String or list object of dimensions to be used. If blank it will all dimensions.

• keep_item – String object of which records to keep. I.e. either ‘last’ or ‘first’.

• data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘data’, ‘block’, ‘slice’

Returns

data object without duplicate records

rename_dim_item(dim, old, new, data_obj='data')

Give a new name to an existing name in a dimension. I.e. if ‘USA” is to be changed to ‘North America’

Parameters

• dim – String value of which dimension contains the item you want to change. I.e. ‘Region’

• old – String value of the item to be changed. I.e. ‘USA’

• new – String value of the new name of the item. I.e. ‘North America’

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

data object

rename_dimensions(dim_list, data_obj='data')

Give new names to one or all of the dimension names. I.e. if dimensions are [‘Department’, ‘Region’, ‘Data_Block’] it can be changed to [‘Department’, ‘Geography’, ‘Data_Block’] :param dim_list: List of all the dimension names with the new names included :param data_obj: Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’,

‘function_result’, ‘variance’

Returns

data object

reorder_dimensions(new_order, data_obj='data')

Change the order of the dimensions of the index.

Parameters

• new_order – List input i.e. [‘Department’, ‘Region’, ‘Data_Block’, ‘Line Item’]

• data_obj – Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’, ‘function_result’, ‘variance’

Returns

data_obj

reorder_index_dim(new_order, dim=None, axis_target='index', data_obj='data')

Change the order of the item within an index. I.e. if existing the Line Item dimension has an existing order of [‘Office Supplies’, ‘Rent’, ‘Payroll’], change it to [‘Payroll’, ‘Rent’, ‘Office Supplies’].

Parameters

new_order – List values of the new order. Should contain all the values.

I.e. [‘Payroll’, ‘Rent’, ‘Office Supplies’] :param dim: Dimension to be reordered :param axis_target: Index or the columns. I.e. ‘index’ or ‘columns’ :param data_obj: Which data object you want o effect. Available - ‘block’, ‘data’, ‘slice’, ‘consolidation’,

‘function_result’, ‘variance’

Returns

save_project(prj_name=None, path_name=None)

Saves a project as either a directory with pickle files or as a json object.

Parameters

• prj_name – String object for name of the project. Will make a directory in path_name if it doesn’t exist.

• path_name – Path to directory or a full file path for json. I.e. for json file ‘C:/Budgets/Budgets v1.json’ or ‘C:/Budgets/’ for normal save

Returns

nothing

slice_data(dims=None, dim_values=None, col_range=None, col_list=None, data_obj='data', append_to=False)

Slice and dice data based on dimensions for the index and ranges or lists for the columns.

Parameters

• dims – List object (even for one item) of dimensions. I.e. [‘Department’, ‘Line Item’]

• dim_values – List and nested list object (even for one item) of items on which to slice. I.e. [‘Operations’, [‘Network Costs’, ‘Payroll’]]

• col_range – List object of start and end point of range. I.e. [‘2022-06-30’, ‘2022-12-31’] for datetime

• col_list – List object columns. I.e. [‘2022-06-30’, ‘2022-09-30’, ‘2022-12-31’] for datetime

• data_obj – Which data object you want o effect. Available - ‘data’, ‘slice’

• append_to – Add data to existing slice data object.

Returns

self.slice

slice_to_project(prj_name=None, path_name=None)

Saves the slice data object as a project as either a directory with pickle files or as a json object.

Parameters

• prj_name – String object for name of the project. Will make a directory in path_name if it doesn’t exist.

• path_name – Path to directory or a full file path for json. I.e. for json file ‘C:/Budgets/Budgets v1.json’ or ‘C:/Budgets/’ for normal save

Returns

nothing

subtract_dim(dim_name, dim_vals=None, calc_name='New Item', data_obj='slice')

Subtract two or more dimension items. For example, ‘Total_Revenue’ - ‘COGS’ with FPA_OBJECT.subtract_dim(‘IS_Category’, [‘Total_Revenue’, ‘COGS’]

Parameters

• dim_name – String object of dimension name. I.e. ‘IS_Category’.

• dim_vals – List object of dimension items to multiply. I.e. [‘Total_Revenue’, ‘COGS’]

• calc_name – String object of new dimension item name. I.e. ‘Gross Profit’

• data_obj – Which data object you want o effect. Default: ‘slice’. Available - ‘data’, ‘slice’

Returns

self.function_result

sum_dim(dim_name, dim_vals=None, calc_name='New Item', data_obj='slice')

Add two or more dimension items. For example, ‘Rent’ + ‘Office Supplies’ with FPA_OBJECT.sum_dim(‘Line Item’, [‘Rent’, ‘Office Supplies’]

Parameters

• dim_name – String object of dimension name. I.e. ‘Line Item’.

• dim_vals – List object of dimension items to multiply. I.e. [‘Rent’, ‘Office Supplies’]

• calc_name – String object of new dimension item name. I.e. ‘Office Expense’

• data_obj – Which data object you want o effect. Default: ‘slice’. Available - ‘data’, ‘slice’

Returns

self.function_result

time_slice(dim=None, start_dt=None, end_dt=None, idx_period=None, col_period=None, data_obj='data')

update_custom_xl(f_path=None, ws_name=0, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None)

Custom update of existing data with mapping for data and dimensions from Excel Worksheet. It will update a section of ‘data’, which has an identical dimension structure (excluding ‘Data_Block’ with new data. The new block’s meta information will be referenced in the original blocks data to show history.

Parameters

• f_path – Path to the Excel File.

• ws_name – Name or index number of the worksheet. i.e. ‘Accounts’ or 2.

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

update_xl_directories(dir_path, xl_id=None, ws_name=0, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None)

Imports dimensions and table from a worksheet from all the Excel files (with or without identifiers) from a series of directories. It will update a section of ‘data’, which has an identical dimension structure (excluding ‘Data_Block’ with new data. The new block’s meta information will be referenced in the original blocks data to show history.

Parameters

• dir_path – Path to the root directory containing the directories which contain the files.

• xl_id – String used to filter files to extract data, i.e. if file name is “Budget v3.xlsx” you could say ‘v3.xlsx’

• ws_name – Worksheet to read. Input as string i.e. ‘Sales’ or index. Zero is default.

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

Returns

self.data

update_xl_directory(dir_path, xl_id=None, ws_name=0, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None)

Updates dimensions and table from a worksheet from all the Excel files (with or without identifiers) from a directory. It will update a section of ‘data’, which has an identical dimension structure (excluding ‘Data_Block’ with new data. The new block’s meta information will be referenced in the original blocks data to show history.

Parameters

• dir_path – Path to the directory containing the files

• xl_id – String used to filter files to extract data, i.e. if file name is “Budget v3.xlsx” you could say ‘v3.xlsx’

• ws_name – Worksheet to read. Input as string i.e. ‘Sales’ or index. Zero is default.

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

Returns

self.data

update_xl_sheets(f_path, wb_sheets=None, table_coords=None, idx_cols=0, dim_names=None, dim_vals=None, dim_names_coords=None, dim_coords=None)

Updates all the tables from worksheets within an Excel file. It will update a section of ‘data’, which has an identical dimension structure (excluding ‘Data_Block’ with new data. The new block’s meta information will be referenced in the original blocks data to show history.

Parameters

• f_path – Path to the file

• wb_sheets – Worksheets to read. Input as list i.e. [‘Sales’, ‘Operations’]

• table_coords – Excel references for top left and bottom right of table. i.e. [‘B7’, ‘Z20’].

• idx_cols – Number of index columns to use. 0 would be the first column and 3 would mean first 3 columns.

• dim_names – New dimensions names to add. i.e. [‘Department’, ‘Geography’]

• dim_vals – Values for new dimensions. i.e. [‘Sales’, ‘North America’]

• dim_names_coords – Excel references to get dimension names from the Excel. i.e. [‘A3’, ‘A4’].

• dim_coords – Excel references to get dimension values from the Excel. i.e. [‘B3’, ‘B4’].

Returns

self.data

variance_analysis(dim_name, dim1, dim2, data_obj='data')

Returns and difference and percent difference analysis based on two items with in a dimension. I.e. x.variance_analysis(‘Type’, ‘Actual’, ‘Budget’) will produce the ‘variance’ data object with amount and percent differences.

Parameters

• dim_name – String object of the name of dimension. I.e. ‘Type’ or ‘Region’.

• dim1 – String object of the name of dimension item for first part of calculation. I.e. ‘Actual’ or ‘North’.

• dim2 – String object of the name of dimension item for comparison. I.e. ‘Budget’ or ‘South’.

• data_obj – Which data object you want o effect. Default: ‘data’. Available - ‘data’, ‘slice’

Returns

self.variance