Wrote a python code to read the monthly Visa issuance spreadsheet and calculate the totals.
if interested use the code. I'm new to Python, if anyone is can tweak, you are more than welcome.
here is my amateur python code.
################################################## #################################
import openpyxl
wb = openpyxl.load_workbook("NOVEMBER 2017 - ** Issuances by FSC and Visa Class.xlsx")
sheet_names = wb.get_sheet_by_name('Table 1')
##Country={}
visa_iss_EB1 = ['E11', 'E12', 'E13','E14', 'E15']
visa_iss_EB2 = ['E21','E22','E23']
visa_iss_EB3 = ['E31','E32','E34','E35','EW3','EW4','EW5']
visa_iss_EB4 = ['BC1','SF1','BC2','SF2','BC3','SF3','SD1','SG1','S D2','SG2','SD3','SH1','SE1','SH2','SE2','SJ1','SE3 ','SJ2','SK1','SN2','SK2','SN3','SK3','SN4','SK4', 'SR1','SL1','SR2','SN1','SR3']
visa_iss_EB5 = ['C51','C52','C53','T51','T52','T53','R51','R52','R 53','I51','I52','I53']
print("Start reading damn reading")
IND_EB1_Sum = 0
CHI_EB1_Sum =0
MEX_EB1_Sum =0
PHI_EB1_Sum =0
ROW_EB1_Sum =0
IND_EB2_Sum = 0
CHI_EB2_Sum =0
MEX_EB2_Sum =0
PHI_EB2_Sum =0
ROW_EB2_Sum =0
IND_EB3_Sum = 0
CHI_EB3_Sum =0
MEX_EB3_Sum =0
PHI_EB3_Sum =0
ROW_EB3_Sum =0
IND_EB4_Sum = 0
CHI_EB4_Sum =0
MEX_EB4_Sum =0
PHI_EB4_Sum =0
ROW_EB4_Sum =0
IND_EB5_Sum = 0
CHI_EB5_Sum =0
MEX_EB5_Sum =0
PHI_EB5_Sum =0
ROW_EB5_Sum =0
row_count = sheet_names.max_row
for row in range(2, row_count + 1):
Country_name = sheet_names['A' + str(row)].value
Visa_Class = sheet_names['B' + str(row)].value
Issuances = sheet_names['C' + str(row)].value
for EB1 in visa_iss_EB1:
if Country_name == 'India':
if Visa_Class == EB1:
IND_EB1_Sum = Issuances+IND_EB1_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'China - mainland born':
if Visa_Class == EB1:
CHI_EB1_Sum = Issuances+CHI_EB1_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'Mexico':
if Visa_Class == EB1:
MEX_EB1_Sum = Issuances+MEX_EB1_Sum
elif Country_name == 'Philippines':
if Visa_Class == EB1:
PHI_EB1_Sum = Issuances+PHI_EB1_Sum
else:
if Visa_Class == EB1:
ROW_EB1_Sum = Issuances+ROW_EB1_Sum
for EB2 in visa_iss_EB2:
if Country_name == 'India':
if Visa_Class == EB2:
IND_EB2_Sum = Issuances+IND_EB2_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'China - mainland born':
if Visa_Class == EB2:
CHI_EB2_Sum = Issuances+CHI_EB2_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'Mexico':
if Visa_Class == EB2:
MEX_EB2_Sum = Issuances+MEX_EB2_Sum
elif Country_name == 'Philippines':
if Visa_Class == EB2:
PHI_EB2_Sum = Issuances+PHI_EB2_Sum
else:
if Visa_Class == EB2:
ROW_EB2_Sum = Issuances+ROW_EB2_Sum
for EB3 in visa_iss_EB3:
if Country_name == 'India':
if Visa_Class == EB3:
IND_EB3_Sum = Issuances+IND_EB3_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'China - mainland born':
if Visa_Class == EB3:
CHI_EB3_Sum = Issuances+CHI_EB3_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'Mexico':
if Visa_Class == EB3:
MEX_EB3_Sum = Issuances+MEX_EB3_Sum
elif Country_name == 'Philippines':
if Visa_Class == EB3:
PHI_EB3_Sum = Issuances+PHI_EB3_Sum
else:
if Visa_Class == EB3:
ROW_EB3_Sum = Issuances+ROW_EB3_Sum
for EB4 in visa_iss_EB4:
if Country_name == 'India':
if Visa_Class == EB4:
IND_EB4_Sum = Issuances+IND_EB4_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'China - mainland born':
if Visa_Class == EB4:
CHI_EB4_Sum = Issuances+CHI_EB4_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'Mexico':
if Visa_Class == EB4:
MEX_EB4_Sum = Issuances+MEX_EB4_Sum
elif Country_name == 'Philippines':
if Visa_Class == EB4:
PHI_EB4_Sum = Issuances+PHI_EB4_Sum
else:
if Visa_Class == EB4:
ROW_EB4_Sum = Issuances+ROW_EB4_Sum
for EB5 in visa_iss_EB5:
if Country_name == 'India':
if Visa_Class == EB5:
IND_EB5_Sum = Issuances+IND_EB5_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'China - mainland born':
if Visa_Class == EB5:
CHI_EB5_Sum = Issuances+CHI_EB5_Sum
##print(Country_name,Visa_Class,Issuances)
elif Country_name == 'Mexico':
if Visa_Class == EB5:
MEX_EB5_Sum = Issuances+MEX_EB5_Sum
elif Country_name == 'Philippines':
if Visa_Class == EB5:
PHI_EB5_Sum = Issuances+PHI_EB5_Sum
else:
if Visa_Class == EB5:
ROW_EB5_Sum = Issuances+ROW_EB5_Sum
print ("\n")
print("Total China EB1 is",CHI_EB1_Sum)
print("Total India EB1 is",IND_EB1_Sum)
print("Total Mexico EB1 is", MEX_EB1_Sum)
print("Total Philippines EB1 is", PHI_EB1_Sum)
print("Total ROW EB1 is", ROW_EB1_Sum)
print("Grand Total of EB1", CHI_EB1_Sum+IND_EB1_Sum+MEX_EB1_Sum+PHI_EB1_Sum+RO W_EB1_Sum)
print ("\n")
print("Total China EB2 is",CHI_EB2_Sum)
print("Total India EB2 is",IND_EB2_Sum)
print("Total Mexico EB2 is", MEX_EB2_Sum)
print("Total Philippines EB2 is", PHI_EB2_Sum)
print("Total ROW EB2 is", ROW_EB2_Sum)
print("Grand Total of EB2", CHI_EB2_Sum+IND_EB2_Sum+MEX_EB2_Sum+PHI_EB2_Sum+RO W_EB2_Sum)
print ("\n")
print("Total China EB3 is",CHI_EB3_Sum)
print("Total India EB3 is",IND_EB3_Sum)
print("Total Mexico EB3 is", MEX_EB3_Sum)
print("Total Philippines EB3 is", PHI_EB3_Sum)
print("Total ROW EB3 is", ROW_EB3_Sum)
print("Grand Total of EB3", CHI_EB3_Sum+IND_EB3_Sum+MEX_EB3_Sum+PHI_EB3_Sum+RO W_EB3_Sum)
print ("\n")
print("Total China EB4 is",CHI_EB4_Sum)
print("Total India EB4 is",IND_EB4_Sum)
print("Total Mexico EB4 is", MEX_EB4_Sum)
print("Total Philippines EB4 is", PHI_EB4_Sum)
print("Total ROW EB4 is", ROW_EB4_Sum)
print("Grand Total of EB4", CHI_EB4_Sum+IND_EB4_Sum+MEX_EB4_Sum+PHI_EB4_Sum+RO W_EB4_Sum)
print ("\n")
print("Total China EB5 is",CHI_EB5_Sum)
print("Total India EB5 is",IND_EB5_Sum)
print("Total Mexico EB5 is", MEX_EB5_Sum)
print("Total Philippines EB5 is", PHI_EB5_Sum)
print("Total ROW EB5 is", ROW_EB5_Sum)
print("Grand Total of EB5", CHI_EB5_Sum+IND_EB5_Sum+MEX_EB5_Sum+PHI_EB5_Sum+RO W_EB5_Sum)
print ("\n")
print("Grand Total for China", CHI_EB1_Sum+CHI_EB2_Sum+CHI_EB3_Sum+CHI_EB4_Sum+CH I_EB5_Sum)
print("Grand Total for India", IND_EB1_Sum+IND_EB2_Sum+IND_EB3_Sum+IND_EB4_Sum+IN D_EB5_Sum)
print("Grand Total for Mexico", MEX_EB1_Sum+MEX_EB2_Sum+MEX_EB3_Sum+MEX_EB4_Sum+ME X_EB5_Sum)
print("Grand Total for Philippines", PHI_EB1_Sum+PHI_EB5_Sum+PHI_EB4_Sum+PHI_EB3_Sum+PH I_EB2_Sum)
print("Grand Total for ROW", ROW_EB1_Sum+ROW_EB2_Sum+ROW_EB3_Sum+ROW_EB4_Sum+RO W_EB5_Sum)
