vendredi 4 mars 2016

How to make the percentages equal using a formula function in python for the values pulled from Excel

import xlrd,numpy
excel = '/Users/Bob/Desktop/'

wb1 = xlrd.open_workbook(excel + 'assignment3.xlsx')
sh1 = wb1.sheet_by_index(0)

colA,colB = [],[]
for a in range(3,sh1.nrows):
    colA.append(int(sh1.cell(a,0).value))
    colB.append(int(sh1.cell(a,1).value))
print(colA)
print(colB)

given_per = (sh1.cell_value(2,1))*100
print("Given Percentage:", given_per)

calc_per = numpy.irr(colB) * 100  # Using this formula to get the 5%
print("Calculated Percentage", round(calc_per, 0), '%', '\n')

#Given percentage (10%) is the required percentage.We are getting 5%.
#Change values 1275 and 400 because they have the word "change" beside 
#them in a case when given_per > calc_per such that the adjustment
#of those values make the calc_per >= given_per. 
# NOTE: Don't make a change directly in the excel file.

if calc_per >= given_per:
    print("Percentages are equal.")  # In our case they aren't.
else:
    print("Percentages are NOT equal.")
    # Adjust 1275 and 400. Print the adjusted colB as new_colB
    # Print the adjusted values in B6 and B8
    # And display the re-calculated calc_per as new_calc_per
    # NOTE: Make sure that this code is generic. It should work for any excel
    # file of the same format. The word "change" could be in any row. Keep that in mind.
    # Don't forget to skip/ignore the first two rows and the third column.
    for i in range(4,sh1.nrows):                   # *****
        if sh1.cell(i,3).value == "change":
            for b in int(sh1.cell(i,1).value):
                calc_per = numpy.irr(colB) * 100
                if calc_per < given_per:
                    for i in range(4,sh1.nrows):
                        if sh1.cell(i,3).value == "change":
                            for b in sh1.cell(i,1).value:
                                b+1;
                else:
                    break                          # *****
    # Remember to use the formula to check the percentages.
    #print("With adjusted values, the percentages are now equal,"
    #      "or calc_per is greater than given_per.")

Please go through the comments to understand the code. The part I am struggling with are the lines from starting and ending with #*****.

What we need to do in this assignment is to equal the "Given Percentage" value of 10% by using the formula function numpy.irr as shown in line 24 of the code. With the current values in column B we get only 5%, so we need to adjust the values in B6 and B8 such that when we use the numpy.irr again, we get a percent value that is 10% or above. Basically, we need the calc_per >= given_per.

When I used excel solver to get that calc_per to be 10%, the new values in B6 and B8 were 1661.45 and 434.39 respectively. Obviously, they could be anything. This is what excel produced.

Snippet of the excel file is below as an image:

Excel File Snippet

Thanks!

Aucun commentaire:

Enregistrer un commentaire