They do not handle the calculation and remittance of sales tax on in-app purchases (Apple does this).
That means I had to write code to calculate the sales tax we have to pay to each NC county, because we have to do this on a quarterly basis. It was such a fucking waste of time in order to pay Wake County like $4.25.
read more
ncCountyByZip.csv
ncCountyTaxByCounty4-1-17.csv
groupZipByCounty.py (inline below as well)
Automated Guide 1. Go here: https://www.dor.state.nc.us/electronic/salesanduse.html and scroll down to "File and/or pay E-500". 2. Enter in your details. 3. Enter in account number: [redacted]. Make sure the next screen says "Coursicle Inc" 4. Select "File E-500 and pay full amount online" or "File zero tax due E-500". 5. Select month, day, year ending (should be the month prior to the current month you're doing this). 6. Enter in EIN. 7a. Make note of any monies received for sales made, like contracts!. 7b. Go to https://play.google.com/apps/publish/?account=5470096802127841529#ReportsPlace and navigate to the "Estimated Sales Reports" and download the three months before the current one. 7c. Go through each of the downloaded CSVs, add them to the quarter folder (Online Filing ... x/xx/xx), and remove any of the refund numbers from the CSV. Rename them to "januarySales", "februrarySales", etc. 7d. Copy the ZIP column of each of the three CSVs, and paste it into the top of the python file. 7e. Go to this link (https://www.dor.state.nc.us/taxes/sales/taxrates.html) to see if the top says "Tax Rates Effective xxxx" and if that xxxx is different from the CSV file we are using in Python, then go there and get the "By 5-digit zip" doc, use Tabula to make it into a CSV and save it as a new CSV and update the reference in Python. 7f. (optional) If it's been a while, it's unlikely but which ZIP corresponds to which county could have changed. Can go here and save the page as a PDF. http://www.zipcodestogo.com/North%20Carolina/. Then open up the Tabula application (in this folder) and import the table. Save it as a CSV and feed it into the Python program. 8. Run the Python program. 9. Enter in the values outputted by the program. REMEMBER ANY EXTRA SALES FROM 7A. 10. Screenshot all the pages to the desktop before submission so there's a record. 11. Save these screenshots (make sure there's no sensitive info in them) to the Dropbox.
#!/usr/bin/python
import pprint
import sys
fullZipString = """
28104
10027
61801
60459
"""
fullZipList = fullZipString.splitlines()
ncZips = []
for zipCode in fullZipList:
if zipCode[:2] == "27" or zipCode[:2] == "28":
ncZips.append(zipCode)
print "There were",len(fullZipList),"total sales made this quarter."
print "Of those,",len(ncZips),"were NC purchases."
print "NOTE: please check for and remove NC purchases that were refunded (have a negative sign)."
countyDataRaw = open('ncCountyByZip.csv').read().splitlines()
#organize the data to be a dictionary
countyByZip = {}
for line in countyDataRaw:
splitLine = line.split(",")
thisZip = splitLine[0]
thisCounty = splitLine[2]
if thisZip in countyByZip:
print "POTENTIAL ERROR: our zip code source data has this zip twice:",thisZip
#sys.exit(1)
countyByZip[thisZip] = thisCounty
#pprint.pprint(countyByZip)
numberOfSalesByCounty = {}
for zipCode in ncZips:
countyForZip = countyByZip[zipCode] #if this fails, then this zip is not NC. Use this to figure out ZIP: https://m.usps.com/m/ZipLookupAction. If it's invalid, look up the state/county in the excel and enter a valid ZIP for that county in place of it.
if countyForZip not in numberOfSalesByCounty:
numberOfSalesByCounty[countyForZip] = 1
else:
numberOfSalesByCounty[countyForZip] += 1
print "Number of purchases by county:"
pprint.pprint(numberOfSalesByCounty)
print "\n\nFirst page:\n\n"
#now go through and figure out what the tax is for every county
taxesByCounty = {}
taxByCountyDataRaw = open('ncCountyTaxByCounty4-1-17.csv').read().splitlines()
for line in taxByCountyDataRaw:
splitLine = line.split(",")
#skip the headers
if not splitLine[0].isdigit():
#print "Skipping",splitLine
continue
thisCounty = splitLine[2]
thisCountyTax = float(splitLine[4].replace("%",""))/100
thisCountyTransitTax = float(splitLine[6].replace("%",""))/100
if thisCounty in taxesByCounty:
continue
else:
taxesByCounty[thisCounty.title()] = {"tax":thisCountyTax, "transitTax":thisCountyTransitTax}
#pprint.pprint(taxesByCounty)
costOfProduct = 4.99
totalReciepts = costOfProduct * sum(numberOfSalesByCounty.values())
print "North Carolina Gross Receipts:", totalReciepts
print "Gen. State Rate. Reciepts:", totalReciepts, "Tax:", totalReciepts * 0.0475
twoPercentReciepts = costOfProduct * sum([numberOfSalesByCounty[county] for county in numberOfSalesByCounty if taxesByCounty[county]["tax"] == .02])
print "2% County Rate. Reciepts:", twoPercentReciepts, "Tax:", twoPercentReciepts * .02
twoTwoFivePercentReciepts = costOfProduct * sum([numberOfSalesByCounty[county] for county in numberOfSalesByCounty if taxesByCounty[county]["tax"] == .0225])
print "2.25% County Rate. Reciepts:", twoTwoFivePercentReciepts, "Tax:", twoTwoFivePercentReciepts * .0225
pointFivePercentTransitReciepts = costOfProduct * sum([numberOfSalesByCounty[county] for county in numberOfSalesByCounty if taxesByCounty[county]["transitTax"] == .005])
print ".5% Transit County Rate. Reciepts:", pointFivePercentTransitReciepts, "Tax:", pointFivePercentTransitReciepts * .005
pointTwoFivePercentTransitReciepts = costOfProduct * sum([numberOfSalesByCounty[county] for county in numberOfSalesByCounty if taxesByCounty[county]["transitTax"] == .0025])
print ".25% Transit County Rate. Reciepts:", pointTwoFivePercentTransitReciepts, "Tax:", pointTwoFivePercentTransitReciepts * .0025
print "\n\nNext page:\n\n"
#calculate the tax by county
for county in sorted(numberOfSalesByCounty.keys()):
countyTwoPercentTax = numberOfSalesByCounty[county] * costOfProduct * .02 if taxesByCounty[county]["tax"] == .02 else 0
countyTwoTwoFivePercentTax = numberOfSalesByCounty[county] * costOfProduct * .0225 if taxesByCounty[county]["tax"] == .0225 else 0
transitPointFivePercentTax = numberOfSalesByCounty[county] * costOfProduct * .005 if taxesByCounty[county]["transitTax"] == .005 else 0
print county
print "2%:", countyTwoPercentTax
print "2.25%:", countyTwoTwoFivePercentTax
print "0.5%:", transitPointFivePercentTax
print ""