Extract and Process Data from Excel with Python

# Overview The goal is to calculate the commissions owed to a series of affiliates, based on monthly customer sales. Data is contained within Excel files. # Data Sources Data is being provided by a third party in the form of Excel spreadsheets. # Technical Specifications - The program will be either a command line or GUI application to be run my computer - Code to be written in Python 3 (version 3.4.3, 64 bit). Code should generally conform with PEP 0008 - The program will be executed on a Windows 10 machine - Excel 2013 is also installed on this machine - Other dependencies should be packaged with the source code, or installable via ‘pip’ - Internal data could be stored in either another Excel file or a file-based database, like sqlite (your choice) - Configuration can either be in additional Excel files or text files (your choice) - Exported reports can be either text files, Excel files, HTML files, or PDF files (your choice) - A test suite, showing that the various commission structures are applied correctly, will need to be part of the project - The program should be versioned as per Semantic Versioning 2.0.0. The base version of the program will be version 2.0.0 # User Interaction 1. The user will download newest data files 2. The user will update the configuration files, if needed. These configuration files will specify commission schedules and affiliate code groups 3. The user invokes the program. Program outputs reports showing, for each affiliate, what is owed them for commissions. The reports will list the date processed, and the version of the software used # Anticipated (Internal) Program Flow 1. Collect data file from downloaded location 2. Extract required data from Excel files 3. Sanity check the data 4. Apply affiliate code overrides (as specified in the configuration files) 5. Determine the commission owed, account by account, then month by month (as specified in the configuration files) 6. Summarized the data 7. Export the reports # Commission Structure Commission on an account can be paid several ways. Here is an overview of the different ways. A file providing the commission that applies, based on the billing date and the affiliate code will be part of the configuration files. The applicate commission structure will depend on whether the account is a residential or business account. The applicable commission structure may also change with time (i.e. structure A until September 2015, structure B afterwards). Commission may also be payable to more than one affiliate (i.e. manager’s overrides). 1. Straight $X at the time of sign up. This is prorated over Y months if the account cancelled (within that Y months) 2. Straight $X for each month the account is active 3. $X for each 1000 kWh of usage at the time of sign up. Usage would be determined from the average usage over the first Y months the account is active. This is prorated over Z months if the account cancelled (within that Z months) 4. $X for 1000 kWh of usage in the month (prorated) 5. X% of net income. See section below on how the Net Income is calculated. 6. X% deduction of any ‘write offs’ 7. X% deduction of additional prudential required. See section below on how the Prudential Requirements are calculated ## Net Income Calculations Net Income is Gross income, minus: - Credit check fee (applies to certain rates) - Interest paid on customer’s prudential contribution ## Prudential Requirement Calculations Prudential requirements are given to use by the third party as the sum for all accounts, and so the amount for any one particular account will be prorated based on the account’s last months’ usage. (Note 1000 kWh = 1 mWh). - AESO charges (for both months) apply for both months - There is also a WIRE area charge # Bidding on the Project When you bid on this project, start your bid with "Burst Energy is great!", so that I know you actually took the time to read this spec. This helps me in eliminating canned bids.