Customize tax calculation to accommodate U.S. zip code rates / state tax rate

[bobmeetin]

In many U.S. states tax must be collected (any paid) in accordance with both the base state tax rate and zip code rates within the state. There may be several  thousand custom tax rates within some states like California or New York. The eCommerce solution must be able to handle these various rates. The simple one tax rate per state does not work.

State tax + zip code zone tax (or whatever you call it) = total tax rate percentage.

In VM 1.1.x this was done through a hack which involved editing ps_checkout.php, adding or including an array of zip codes and tax rates, and making a change to the tax computation to add the zip code rate percentage to state rate to get total percent.  This total percent rate gets used to compute the total tax.

The zip codes / rates can be stored in either a flat file or mysql table. This solution does not warrant making any frontend admin form changes only identifying the correct code and adding the change to include zip code tax. The store owner is responsible for updating the table or flat file. I can provide an import script, but I am not familiar with the VM 2.x code to make the calculation changes.

This hack will solve the urgent need. Who can help?

[911websiterepair]

we developed this a year ago for vm1..1.x     no one wanted it,  we did not upgrade ot for 2.0 and maintaining the tax tables is a huge problem as every state has its own info and format


there is a company that does this, and maintains the tax tables per state. but it is not developed for virtuemart,   they do have an api available though

try Carl Stefanelli
fed-tax.net
or
taxcloud.net

they may be able to help you

206-452-1686

his email is
cstefanelli@fed-tax.net

[bobmeetin]

I can't imagine why no one wanted it unless I'm misunderstanding. In the U.S. if you are going to run an online shop your shop must be capable of collecting taxes per the state tax needs which are governed by zip codes (and regions and transit districts). I'd imagine that some shop owners may choose to not deal with it, legally, and perhaps collect the state tax only, but that is foolish. The risks are high.

Obtaining the tax tables is no big problem. For a non-VM shop we went to zip2tax.com/ and purchased the list for Colorado. I then wrote a fairly simple import script to import the zip codes and rates into the tax config tables. There is no need for any administration forms (for me), maybe for others who need an import function.

I can do the same for vm, import the spreadsheet into a zip code/rate table, but this will do no good at this time as VM2 is lacking the tax calculation to close the loop. Is this something you're familiar with?

[911websiterepair]

we basically did the same thing,   but the component had to work for all states,  not just one, and at that time the available formats of the tax spreadsheets did not work for vm.

i was just sharing my experience,   you also have to remember, no one wants a commercial component

one other factor, is that there is a benchmark sales threshold in some states,    for instance you in some states you only have to do destination tax if sales are over 50k per month

one other consideration, in some states there are multiple levels of tax which we had taken into consideration

state sales tax
county sales tax
city sales tax

but this will do no good at this time as VM2 is lacking the tax calculation to close the loop. Is this something you're familiar with?
response: yes,.    and is a huge problem

[bobmeetin]

"no one wants a commercial component" - well, er, some of us feel compelled to follow the letter of the law and not incur the rath of big brother than constantly watch over our vigilent shoulders.  And as well, the time I've wasted researching this solution is many times what it would cost for the component.  I also feel this should be a core VM2 function, for what it's worth.

My first experience with VM was with 1.0.x. someone created the hack to work with US zip codes. much thanks there. For VM 1.1.+ there was similar need and still no core component. Not being VM framework skilled I was able to convert the 1.x hack to 1.1.+ (the core file containing computations being ps_checkout.php) such that it pulled the necessary data out of an array.

Destination tax benchmark? OMG I never heard of this. Any chance you know which states are in that group. I could call the taxation department in Colorado to see if there is anything like that here. All things considered, a sales benchmark would make much sense. The paperwork to process miniscule sales is a time-waster. The other thing about paperwork is that some of these local municipalities also request that you register/file local returns for each zip.  How heavenly!

You missed County in your list. Believe it or not there are cities in Colorado such as Erie that have a single zip code but are literally split into multiple counties. If you live in county #1 and zip 12345 you pay 5.3% + state base, but in county # 2 (across the street) you pay .4% + state base. So to really be a stickler the seller profile should also collect the county. I'm not suggesting this, just verbalizing.

What a mess!

[911websiterepair]

your right about everything,  call you state and find out what the benchmark is,   most stores never get anywhere near that

[jenkinhill]

I also feel this should be a core VM2 function, for what it's worth.

Why would the many thousands of users outside the US want to have US zip code data in their installation? It is already difficult to keep the install files small enough so that people can actually install VM on their shared hosts.

Interesting issue, though.

[911websiterepair]

good point jenkinhill
virtuemart is a component that needs to fit the needs internationally

so, this justifies this tax functionality needs to be a installable component, the tax tables need to be updated on a regular basis, and handle all states appeal to the large US user base of virtuemart

I could initiate development again, but i need to have 20 people who will purchase it to justify development. the one we made for 1.x.x series was a component, and one file needed to be hacked. But the overhead for maintaining and formating the tax files for each state made it prohibitive. I don't know yet if we could make this into an installable component. I have to research

[bobmeetin]

Here is the link to the 1.1.x hack: http://www.dottedi.biz/archives/746/. My involvement was making a few relatively simple changes  to make it work with 1.1.x as it was originally created for 1.1.0. This hack did not involve creating a super program and incorporating US zip codes / zone into the vm package.

I'm not suggesting to make the program really smart and configure it to work with one of the API services that maintains all this information for a small fee and I'm not suggesting that you store US taxes as part of the core. It is the store owner's job to import the data. I have written simple scritps to do this.

zen-cart, magento, ixxocart and hundreds of other ecommerce programs already have the capacity to either import the tax rates into a table or grab the data from a flat file and do a calculation and presto display total tax.

Without this functionality many, many U.S. shop owners have to sin in how they do business. They have to do something kludgey to make it work.

Down the road it will get uglier as U.S. states implement the out of state taxes. Here is a link that discusses the future: http://blog.avalara.com/2012/09/10/are-you-ready-for-online-sales-tax/

Most of the horror is for the shop owner as he/she needs  not only to collect taxes but also, to be legal, to register in a zillion municipalities for the privilege of doing business. We're not asking to solve this problem, just some background.

If I knew the framework and the tax calculation, I would:

1) create a simple, auxiliary table

mysql> select * from jos_aux_taxes;
+----+-------------+----------+
| id | postal_code | tax_rate |
+----+-------------+----------+
|  1   | 80000          | .92       |
|  2   | 80516          | 4.7       |
|  3   | 85000          | 3.8       |
|  4   | 87654          | 6.7       |
+----+-------------+----------+
4 rows in set (0.00 sec)

2) Change the tax computation

if ( $state == "CO" ) { grab the Colorado state entry, 2.9% }
if ( $state == "CO" and $zip  == "85000" ) { grab the tax rate for 85000, 3.8% }

$tax_rate_percentage = $state_rate + $local_zip_rate;

$total_tax = $price * $tax_rate_percentage;

---------------------------

I'm sure you have more considerations, but that is the gut-level simple solution.

[911websiterepair]

thats the idea

its the format of the state tax tables that is a problem,  its not as easy as your example

we had an installable component that gave access to a centralized db on a dedicated server. All the tax tabels were kept and updated here,  Then there were just a few lines of code that needed to be added to one of the core files

doing it was not the problem as what we had worked

its that there is no uniformity between the states and their tax tables, and tax rules. State tax tables are updated on average every three months, and not in a workable format. They each had to be reformatted each time. And they were not small. If i remember right there were over 500000 entries for California alone.

[bobmeetin]

Again, I may be too simple-minded to act like I'm talking intelligently.  When I purchased the tax rate file for Colorado 2 years ago (for ixxocart which was the only solution offering drop-shipping at the time) it came as a simple spreadsheet with a ton of fields including the aforementioned county which I'm ignoring here.  Bottom line - I saved 2 fields, zip code and rate - maybe this is not enough for other states but it got us over the hump. It took a day to learn their table structure and write an import script, no they didn't include one, but saved probably 2 days administration plus errors.  I think there were about 700 zone entries for Colorado.

Regarding the virtuemart hack, someone from California applied the fix about 4 years ago. He mentioned in his blog comment that there were over 4000 entries then.  Maybe that has grown exponentially.  In the case of Colorado, it's a problem, but fairly easy to solve and make you reasonably legal.  You may at some point have to add one or more additional reserved fields to accommodate other situations.  I'm just spitballing here, but perhaps provide some configurable functionality to allow store owners to define a tax calculation based upon their needs, rather than trying to research and guess what the needs are. Chances are that the simple Colorado scenario (maybe with counties) will meet the needs of 80% or so.  But the other part, I don't know that you can be responsible for the variations in the spreadsheets. Like it or not, I tend to think that it would be store owner responsiblity to get the spreadsheet into a standard format that can be imported via a script.

Spam - many spam forms using one of several annoying variations of captcha.  I like the idea of creating configurable spam traps which might be based on math, hidden fields, etc. Same idea with taxes. 

The other problem with the current lack of functionality is time/effort/research is that VM / Joomla are out there for folks, inexperienced folks, to download/install/configure.  It commonly isn't until well after the fact when you've already burned weeks into installing joomla, virtuemart, a favorite theme, some favored extensions, populated the cart and some core website pages, maybe configured the blog, then you go to enable taxes and the egg is dropped on your plate.  Fedex is not available or taxes are not doable.  That is a ton of time invested.  Missing ingredients should be documented so that this waste is eliminated.  Myself, having used vm 1.1+ in the past I knew of the original issue but never in my wildest dreams could I have imagined that this got lost in VM2.x+. 

[911websiterepair]

fedex is available,   and paypalpro too

joomlacache.com

[bobmeetin]

The long and short of this is that this program is no longer usable in the U.S. market. Aside from the hack that worked in 1.1.+ this is a major unresolved problem for U.S. customers. After several costly weeks and expense of setting up a shop with inventory and such I aborted and changed directions.  I looked around, did some software evaluation and decided to switch to zen-cart. There is an off-the-shelf tax solution for zen-cart available using a service called Tax Cloud, www.taxcloud.net. It works. I tested it using various Colorado municipalities and it is correctly computed the taxes. I verified this by looking up the correct tax rates for these municipalities, zones, districts, zip codes everything. It successfully works down to the granularity of street addresses within the same zip code.

[codingmall]

The long and short of this is that this program is no longer usable in the U.S. market. Aside from the hack that worked in 1.1.+ this is a major unresolved problem for U.S. customers. After several costly weeks and expense of setting up a shop with inventory and such I aborted and changed directions.  I looked around, did some software evaluation and decided to switch to zen-cart. There is an off-the-shelf tax solution for zen-cart available using a service called Tax Cloud, www.taxcloud.net. It works. I tested it using various Colorado municipalities and it is correctly computed the taxes. I verified this by looking up the correct tax rates for these municipalities, zones, districts, zip codes everything. It successfully works down to the granularity of street addresses within the same zip code.

We are interested in adding this facility as an extension for US based users. Getting more information on this. Any input is welcome.

[bobmeetin]

It is an extension for zen-cart. You download and install it in the zen-cart method, configure some address and other info in zc and set up some tax classes. You sign up for a taxcloud.net account, set up your business on taxcloud.  The off-the-shelf procedures were rather lacking. I went back/forth with tc for about a week before I got the nuances in hand, got it calculating taxes, then calculating them correctly. If you get to that point I can help with the config.