import requests
import openpyxl
import urllib
import csv
import textwrap
import pprint

wb = openpyxl.load_workbook('IMPORT.xlsx') #name of the excel file you are using
sheet = wb.get_sheet_by_name('Sheet1') #it's Sheet1 in my case, maybe you have more than one sheet or renamed the one you need

#Below the range within the Excel file that will be read, except for the freight charges. The objective here is to get both a 
#distribution of foreign currency packing and freight charges to several things as accounting needs it, and an FX conversion to your domestic currency (JPY for me here). 
#One important restraint is that JPY unit prices can only be integers in the end so some rounding needs to be done. Adapt and ignore if you don't need to do that. 
#Iteratively appending values to a list for modifying them later. This thing accepts one or more ranges so you do not have to 
#rearrange the Excel just for running the program. Single lines (same number twice) work as well. You can have as many one line ranges as you like. 
#Of course coordinates in the excel and positions when reading the foreign currency rates from the web are just examples. All of this is very rigid and would need a lot of adapting for anybody else.
NettoPosten=[]
Range=[]
answer=1
#First the Q&A 
while True:
 while answer==1:
    while True:
      try:
        print('Enter first row in Excel file, where our range starts:')
        rowStart = int(input())
        break
      except ValueError:
        print ('No letters, no floats. Please try again.')#at time of writing did not know what doubles are
    while True:
      try:
        print('Enter the last row of the range.')
        rowLast = int(input())
        break
      except ValueError:
         print ('No letters, no floats. Please try again.')
    if rowLast<rowStart:
            print('No, the other way around. Please start with the highest row.')   
    else:
        for row in range (rowStart, rowLast+1):
              Range.append(str(row))      #Here the appended value is the number of the row in the source Excel file, this is needed for conveniently updating the correct row after calculations are done.
              Range.append(sheet['G'+str(row)].value) #product name
              Range.append(sheet['H'+str(row)].value) #quantity
              Range.append(sheet['R'+str(row)].value) #unit price (FX)
              Range.append(float(sheet['H'+str(row)].value)*float(sheet['R'+str(row)].value)) #amount for each item in foreign currency
    while True:
      try:  
        print('Enter 1 if you would like to give another range. Enter 2 if you would like to distribute packing charges to the previous range. Enter any other number if you  want to proceed to entering and distributing freight charges.')
        answer=int(input())
        if answer!=2:
            NettoPosten=NettoPosten+Range
            del Range[:]
        break
      except ValueError:
        print ('No, you can only enter 1 or any other number. Please try again.')
        
 while answer==2:
  #Now to the packing charges, they need to be added to their items before distributing freight charges
    while True:
      try:
       print('Enter the row that lists the packing charge to be distributed to the previous range')
       rowPacking = int(input())
       packingCharge = float(sheet['R'+str(rowPacking)].value) #Takes the amount of packing charges to be distributed to Excel file
       #Iteratively adding the amounts extracted from Excel file to get RangeTotalNet, the basis for distributing packing charges.
       RangeTotalNet=0
       LastAmountforPackNet = int(len(Range))-1#this specifies the position of those amounts    
       while LastAmountforPackNet > 3: #will exit the loop when there are no more amounts to add
          RangeTotalNet = RangeTotalNet + Range[LastAmountforPackNet]
          LastAmountforPackNet = LastAmountforPackNet-5 #this jumps to the next amount
       PackingChargePerCurUnit = packingCharge / RangeTotalNet #that's how much packing charge per currency unit needs to be added to each item
       #This adds the share of packing charge to each item in the appended list, by monetary value of each.
       #The resulting changed amount is entered in Range, then NettoPosten is appended by Range and Range is cleared
       LastAmountforPackNet = len(Range)-1
       while LastAmountforPackNet > 3:
         Range[LastAmountforPackNet] = Range[LastAmountforPackNet] + PackingChargePerCurUnit * Range[LastAmountforPackNet]
         LastAmountforPackNet = LastAmountforPackNet - 5
       NettoPosten=NettoPosten+Range
       del Range[:]
       RangeTotalNet=0
       break
      except ValueError:
       print ('No letters, no floats. Please try again.')
 #again some Q&A
	while True:
      try:  
        print('Enter 1 if you would like to give another range. Enter any other if you  want to proceed to entering freight charges.')
        answer=int(input())
        if answer==2:
            answer=3
        break
      except ValueError:
        print ('No, you can only enter 1 or any other number. Please try again.')

 if answer!=1 & answer !=2:
  break

#Now to the freight charges shared by the range of items indicated before are entered. 
while True:
  try:
    print('Enter the row that lists the freight charge to be distributed to our range.')
    rowFreight = int(input())
    freightCharge = float(sheet['R'+str(rowFreight)].value) #Takes the amount of freight charges to be distributed from Excel file
    break
  except ValueError:
     print ('No letters, no floats. Please try again.')


#Now leave aside freight for a minute and prepare the conversion.
ConversionDate = str(sheet['P'+str(rowStart)].value) #This date - since we are talking about goods received - is the day the shipment arrived. 

#Below we download the txt file of exchange rates from the bank we use for our conversion date.
#An error is returned if no such file exists. You should not be automating like that, ask your bank for their API.
#The following is dirty, they might change their URL or text file any day. I did it anyway because I am the only programmer and user of the particular script I wrote.
#Improve and ignore the code below if you are at a risk of damaging others. The below is just an example that worked for the particular text file containing the 
#rates I needed, basically plain text tables formatted with tabs in a recurring pattern. 

res = requests.get('http://www.BLABLABLABANK.co.jp/ratequote//quote_' + str(ConversionDate[0:4]) +str(ConversionDate[5:7]) +str(ConversionDate[8:10])+'.txt')
try:
    res.raise_for_status()
except Exception as exc:
    print('There was a problem. Please try again later: %s' % (exc))

BLBLABLATextFile = open('BLABLABLA.txt','wb')
for chunk in res.iter_content(20000):
    BLBLABLATextFile.write(chunk)
BLBLABLATextFile.close()

#Now that is just one giant string including a lot of whitespace. Among other things, often Date length varies, can't just loop and look for absolute character counts. 

data = open('BLABLABLA.txt').readlines()

#Now data has everything arranged in lines, internally seperated by whitespace.
#But still the position of a value or its length in a given line can vary (e.g. USD TTS 100 can become 99) 
#so we cannot just pick characters numerically. So we create another list:
#textwrap makes the character groups in each line a separate string and gets rid of the whitespace.
#Bodacious! 

#this here looks at the lines I needed - your file will have different lines and you will need different currencies
RATES=[]
RATES.append(textwrap.wrap(data[3], width=15))#headers
RATES.append(textwrap.wrap(data[4], width=15))#USD
RATES.append(textwrap.wrap(data[6], width=15))#EUR
RATES.append(textwrap.wrap(data[9], width=15))#SEK

TTS=str(RATES[0][2])

#Change in layout can lead to false quotes, so read the output to make sure there have been none
#look for the 'TTS' and the letters of the currency code you wanted, they are copied from the list

while True:
  try:
      print ('Please choose currency pair: type 1 for USDJPY; 2 for EURJPY or 3 for SEKJPY.') #Just happens I don't have transactions in other currencies
      cur=int(input())
      if cur==1:
         print('The BLABLABLA ' + str(TTS) + ' rate on ' + str(ConversionDate[0:4]) +str(ConversionDate[5:7]) +str(ConversionDate[8:10]) + ' for the currency pair ' + str(RATES[1][1]) + 'JPY is: ' + str(RATES[1][2])) 
         FXR=float(RATES[1][2])
         break 

      if cur==2:
         print('The BLABLABLA ' + str(TTS) + ' rate on ' + str(ConversionDate[0:4]) +str(ConversionDate[5:7]) +str(ConversionDate[8:10]) + ' for the currency pair ' + str(RATES[2][1]) + 'JPY is: ' + str(RATES[2][2])) 
         FXR=float(RATES[2][2])
         break 

      if cur==3:
         print('The BLABLABLA ' + str(TTS) + ' rate on ' + str(ConversionDate[0:4]) +str(ConversionDate[5:7]) +str(ConversionDate[8:10]) + ' for the currency pair ' + str(RATES[3][1]) + 'JPY is: ' + str(RATES[3][2])) 
         FXR=float(RATES[3][2])
         break 
        
      else:
        print ('No, you can only enter 1, 2 or 3. Please try again.')
  except ValueError:
      print ('No, you can only enter 1, 2 or 3. Please try again.')

#Iteratively adding the amounts extracted from Excel file to get FXTotalNet, the basis for distributing freight charges.
LastAmountforNet = int(len(NettoPosten))-1   
FXTotalNet = 0
while LastAmountforNet > 3:
    FXTotalNet = FXTotalNet + NettoPosten[LastAmountforNet]
    LastAmountforNet = LastAmountforNet-5
 
FXTotalGross = FXTotalNet + freightCharge #redundant but kind of nice to look at as an intermediate output

FreightPerCurUnit = freightCharge / FXTotalNet #Factor by which freight charge is distributed per currency unit

#This below adds the share of freight charge to each item in the appended list, by monetary value of each.
#The resulting changed amount is entered in NettoPosten and in the source Excel file, same story as for packing charges
LastAmountforGross = len(NettoPosten)-1
while LastAmountforGross > 3:
  NettoPosten[LastAmountforGross] = NettoPosten[LastAmountforGross] + FreightPerCurUnit * NettoPosten[LastAmountforGross]
  sheet['T'+(NettoPosten[LastAmountforGross-4])] = NettoPosten[LastAmountforGross]
  wb.save('IMPORT.xlsx')
  LastAmountforGross = LastAmountforGross - 5

#This here converts each of these amounts with freight included to foreign currency, then divides by quantity, rounds, 
#then multiplies the rounded unit price to the YEN amount.
#Each result is changed within NettoPosten and added to the source Excel file. 
LastAmountforConversion = len(NettoPosten)-1  

while LastAmountforConversion > 3:
  NettoPosten[LastAmountforConversion-1] = round((NettoPosten[LastAmountforConversion]*FXR)/float(NettoPosten[LastAmountforConversion-2]))
  sheet['Z'+(NettoPosten[LastAmountforConversion-4])] = NettoPosten[LastAmountforConversion-1]#save to your excel to document your calculation
  wb.save('IMPORT.xlsx')

  NettoPosten[LastAmountforConversion] = round(NettoPosten[LastAmountforConversion-1]*float(NettoPosten[LastAmountforConversion-2]))
  sheet['Y'+(NettoPosten[LastAmountforConversion-4])]=NettoPosten[LastAmountforConversion]#save to your excel to document your calculation
  wb.save('IMPORT.xlsx')
  LastAmountforConversion = LastAmountforConversion - 5

#To get some closure, finally the total amount of YEN (due to rounding of unit prices different from just multiplying the Grand Totals to the rate)
TotalYen = 0
LastAmountforTotalYen = len(NettoPosten)-1
while LastAmountforTotalYen > 3:
  TotalYen = TotalYen + NettoPosten[LastAmountforTotalYen]
  LastAmountforTotalYen = LastAmountforTotalYen - 5
  
pprint.pprint(NettoPosten)
print('Foreign Currency Total (Net): ' + str(FXTotalNet))
print('Freight Charges shared: ' + str(freightCharge))
print('Foreign Currency Total (Gross): ' + str(FXTotalGross))
print('TOTAL JPY: ' + str(TotalYen))

EXIT = 2
while EXIT!=1:
    try:
        print('Press 1 to exit.')
        EXIT = input(int())
        break
    except ValueError:
      print ('No, you can only enter 1. Please try again.')