Update a pre-Euro VAT Play Store IAP export to add VAT

iap_google_vat.py

# coding=utf-8
import csv
import re
import json
import urllib2


def unicode_csv_reader(unicode_csv_data, dialect=csv.excel, **kwargs):
	# csv.py doesn't do Unicode; encode temporarily as UTF-8:
	csv_reader = csv.reader(utf_8_encoder(unicode_csv_data),
							dialect=dialect, **kwargs)
	for row in csv_reader:
		# decode UTF-8 back to Unicode, cell by cell:
		yield [unicode(cell, 'utf-8') for cell in row]


def utf_8_encoder(unicode_csv_data):
	for line in unicode_csv_data:
		yield line.encode('utf-8')

# source country
source_country_default = 'FR'

source_country_override = {'tt[a-z][a-z][a-z]\.': 'GB'}

# Euro countries Google doesn't support
excluded_countries = ['MT', 'HR', 'LV']

# countries Google expects pricing for, in addition to Euro countries
forced_countries = ['AE', 'AU', 'BO', 'BR', 'CA', 'CH', 'CL', 'CO', 'CR', 'EG', 'HK', 'ID', 'IL', 'IN', 'JP', 'KR', 'LB', 'MA', 'MX', 'MY', 'NO', 'NZ', 'PE', 'PH', 'PK', 'RU', 'SA', 'SG', 'TH', 'TR', 'TW', 'UA', 'US', 'VN', 'ZA']

# country-to-currency mapping for some countries with multiple currencies on restcountries.eu
forced_codes = {'CH': 'CHF', 'BO': 'BOB', 'CL': 'CLP', 'US': 'USD'}

# special rules to force round numbers, imposed by Google
forced_zero_digits = {'JP': 6, 'KR': 6, 'CL': 6}

# max prices, per country
max_prices = {'RU': 6000000000}

jsonvat = urllib2.urlopen('http://jsonvat.com/')
vat = json.load(jsonvat)

# dict of country code to standard tax rate
tax_rates = {rate['country_code']: rate['periods'][0]['rates']['standard'] for rate in vat['rates']}

tax_rates.update({country: 0 for country in forced_countries})

# fixer.io doesn't have all the currencies required by Google
#jsonrates = urllib2.urlopen('http://api.fixer.io/latest')
# dict of currency code to conversion rate against EUR
#rates = json.load(jsonrates)['rates']
#rates['EUR'] = 1

jsonrates = urllib2.urlopen('http://openexchangerates.org/api/latest.json?app_id=YOUR_APP_ID')
# dict of currency code to conversion rate against USD
rates_usd = json.load(jsonrates)['rates']

rates = {currency: rates_usd[currency] / rates_usd['EUR'] for currency in rates_usd.keys()}

jsoncodes = urllib2.urlopen('http://restcountries.eu/rest/v1/alpha?codes=' + ';'.join(tax_rates.keys()))
codes = json.load(jsoncodes)
# dict of country code to currency code
codes_dict = {country['alpha2Code']: country['currencies'][0] for country in codes}
codes_dict.update(forced_codes)

for country in codes:
	if len(country['currencies']) != 1 and country['alpha2Code'] not in forced_codes:
		print 'Warning: add one of', country['currencies'], 'to the forced_countries dict for', country['alpha2Code']
		print 'Exchange rates are', {currency: 'unknown' if currency not in rates else rates[currency] for currency in country['currencies']}

# dict of country code to conversion rate against EUR
conversion_rates = {country: rates[codes_dict[country]] for country in tax_rates.keys()}

with open('in_app_products_com.frogsparks.mytrails_vat.csv', 'wb') as dest:
	with open('in_app_products_com.frogsparks.mytrails_pre_vat.csv', 'rb') as source:
		source_rows = csv.reader(source)
		dest_writer = csv.writer(dest)

		row_n = 0

		for source_row in source_rows:
			if source_row[1] == 'unpublished': continue
			row_n += 1

			if row_n != 1:
				source_country = source_country_default

				# do we need to override?
				for (exp, country) in source_country_override.iteritems():
					if re.search(exp, source_row[0]):
						source_country = country
						break

				# RE to find the source price
				price_re = re.compile(source_country + '; ([0-9]*);')

				re_search = price_re.search(source_row[6])
				if source_country == source_country_default:
					source_price = int(re_search.group(1))
				else:
					source_price = int(int(re_search.group(1)) * conversion_rates[source_country_default] / conversion_rates[source_country])
					num_zero_digits = forced_zero_digits[source_country_default] if source_country_default in forced_zero_digits else 4
					source_price = int(str(source_price)[:-num_zero_digits] + '00000000000'[:num_zero_digits])
					print 'Override', source_row[0], ': using', source_country, 'price of', int(re_search.group(1))/1000000.0, codes_dict[source_country], 'converted to', source_price/1000000.0, codes_dict[source_country_default]

				dest_prices = source_country_default + '; ' + str(source_price)

				for (country, rate) in tax_rates.iteritems():
					if country not in excluded_countries:
						conversion_rate = conversion_rates[country] if conversion_rates[country] else 1
						dest_price = str(int(source_price * conversion_rate * (1 + rate / 100.0)))
						num_zero_digits = forced_zero_digits[country] if country in forced_zero_digits else 4
						dest_price = dest_price[:-num_zero_digits] + '00000000000'[:num_zero_digits]

						if country in max_prices and int(dest_price) > max_prices[country]:
							dest_price = str(max_prices[country])

						dest_prices += '; ' + country + '; ' + dest_price

				source_row[6] = dest_prices

			dest_writer.writerow(source_row)

Google is treating the IAP prices for Play Store purchases as VAT-included, so developers get even less revenue. This script (which needs some editing to be useful) reads in a Play Store IAP export csv and updates pricing for all countries based on current exchange rates and Euro VAT (no VAT is added for other countries).