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.

Compétences : Excel, Python

en voir plus : what to write on my overview, what is data structures in c, what is data structures, what is data structure in c, what is data structure, what is data in data structure, what is a data structures, what is a data structure in c, what is a data structure, what i need from my manager, what data structure, what are the application of data structure, what are data structures in c, what are data structures, what are data structure, use of data structures, use of data structure, test configuration manager, Technical account manager, structures data, sales commission structures, sales commission structure, requirement spec, prudential, process x

Concernant l'employeur :
( 0 commentaires ) Edmonton, Canada

Nº du projet : #8541605

Décerné à:


Burst Energy is Great! # Executive Summary The following is a proposal for an application that parses commission data from Excel workbooks and stores said data in a local SQLite database for higher level proces Plus

%selectedBids___i_sum_sub_7% %project_currencyDetails_sign_sub_8% CAD en 7 jours
(1 Évaluation)

23 freelance font une offre moyenne de $519 pour ce travail


Hi sir, I am scraping expert, I have did too many similar projects, please check my feedback then you will know. Can you tell me more details? then I will provide demo data for you. Thanks, Kimi

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 5 jours
(110 Commentaires)

Burst Energy is great! Dear, we are Team of French + US. 11 years experience on Access/Excel/VBA automations. I checked your project description, and I confirm I can develop your program. I will focus on user frien Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 9 jours
(52 Commentaires)

"Burst Energy is great!" Hello, I am a professional programmer with several years of Python programming experience, and I can create the commission calculation program (using Excel) as you have described. I would use Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(48 Commentaires)

Burst Energy is great! Hi I'm a Python programmer. I can build your system as either a command line interface, a GUI interface, or as a django framework application using the test server to do the work. I woul Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(8 Commentaires)

Burst Energy is great! Hi Sir/Madam, I'm expert in Python programming and I have experience working with Excel files, so I can help You with this project. Best regards, Fejs

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 7 jours
(131 Commentaires)

Hi, I have written many python automations for excel both windows and linux. I can help you on this I will create a solution that works on both linux and windows and unix so I will use open office bindings.

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(67 Commentaires)

Hello. I actually read the spec that's why my bid is higher than others. I recently done similar project on converting Excel calculations to standalone Python GUI application. You can check reviews from chemistryharris Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 30 jours
(29 Commentaires)

A proposal has not yet been provided

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 6 jours
(59 Commentaires)

Burst Energy is great! I'm up to the task, I'm pretty good with desktop apps, data management and excel/text/html formats handling, I can build a standalone app for windows (portable/setup), I'm pretty good with GUI h Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 6 jours
(15 Commentaires)

Hi, we are a team of experienced programmers very well versed in Python(Pyqt, Pyside, Tkinter, Pygame etc) Programming. we have been through many projects and looking forward for this one. Our team can do this task e Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(19 Commentaires)

Burst Energy is great! HI Contact me for an efficient, perfect, well documented development of your project according to 100% accuracy and requirements, with dedicated support. Thanks

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 7 jours
(17 Commentaires)

Burst Energy is great! Seems to be a project with a lot of calculations and data processing. Since it is not requested any kind of server connection or linux command, could do it simple with excel and vb.net or vba. W Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 7 jours
(2 Commentaires)

A proposal has not yet been provided

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 5 jours
(1 Évaluation)

"Burst Energy is great!" Hello. More 20 years programming experience. I need more details to set real time and price. Regards. -------------------------------------------------------------------------------------- Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(7 Commentaires)

Burst Energy is great! Hi! I have experience with "xlsxwriter" and "xlrd" modules in Python to work with Excel files. I also quite good in writing unit tests as I am ISTQB-certified Automation QA Engineer. So I am su Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 6 jours
(3 Commentaires)

Burst Energy is great! I am an experienced Linux system admin and software developer. I can do this for you with no problem.

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 7 jours
(1 Évaluation)

hello, your proposed work is clear and structured, I own sificientes skills to run this project. I am python programmer, economist and analyzes of softwere ERP support

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(0 Commentaires)

Burst Energy is great! I have been programming in Python for 5 years and feel confident that I can tackle almost any problem-set using Python's built-in libraries, my own custom package libraries, or those found on Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 7 jours
(0 Commentaires)

Burst Energy is great! Sounds straight forward. I can work with excel files no problem, so if you want to configure things via excel -- that works too. The hardest part of the project is making it easy to upgrade if Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(1 Évaluation)

Burst Energy is great! Hi, I can do this program. I have read the technical specifications, user interaction, program flow, data source and all of it. This can be done and will run on a windows 10 computer with 2013 e Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% CAD en 10 jours
(0 Commentaires)