News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

Bulk update pricing

Started by dylanphelan, December 30, 2021, 19:57:02 PM

Previous topic - Next topic

dylanphelan

Hi

I have previous used CSV Improved (Rolandd) to bulk update prices, add products etc.

Tomorrow I need to bulk update all my prices as I registered for VAT.

I will need to reduce all prices by 15% and then add this 15% back using Tax calculations rules.

Any simple tool to bulk update all the prices from a CSV file?

CSV Improved is good, but it's overkill for what I need to do tomorrow.

thanks

Studio 42

You can use Vm Be Pro
Go in import view and import type: price
You only need a csv with 2 columns price+SKU (you can use gtin or alias too)
You can buy it here https://shop.st42.fr/en/products/vm-be-pro-batch-product-edit.htm
Do not forget to check for latest updates using Joomla update tool.
Greats,
Patrick

dylanphelan

Hi.

Can this update using Product ID instead of SKU ?

All I want on the CSV file is product ID and price.

Does this extension add any meta data or similar?

I used another import tool once, and it add test to the meta description and so on. "import by ...."

thanks

AH

Just do a simple SQL command in phpmyadmin

to reduce the price to the ex vat element

update yourprefix_virtuemart_product_prices as p

SET p.product_price = round(p.product_price /1.15,2)

Regards
A

Joomla 3.10.11
php 8.0

Studio 42

IN VM Be Pro, You can directly use the main view and set your price formula
Cost Price : x0.8333 to remove 20% (tax)

AH : Rounding can give bad results

AH

#5
Not sure why you suggest that rounding gives "bad results", give me a real world example of "bad", I have used this method for years in bulk manufacturer price rises and never had an issue

It gives you a cost price to two decimal places, if you don't want to round - remove that element

Suffice to say that you do not need a plugin for this simple bulk task as it can easily be done via a very simple SQL based task
Regards
A

Joomla 3.10.11
php 8.0

Studio 42

I know that using SQL is working.
You can use directly SQL for anything
About rounding, supose you have a vat free price of 20€ this give a no vat rounded price of 16.66 and 19.99 with 20% vat(19.992 exactly), it's why rounded here is bad if you want the exact same price

AH

The point was that SQL lends itself to these very simple (one off) tasks.

It would have been easier for the user if you had said what your concerns were for rounding.  However rounding will occur in VM at some point and there is a real ex-VAt price that will be to 2 decimals for most businesses.

So it can be achieved using this simple SQL, if you prefer a possible 3 decimals for your ex-VAt price (My advice is don't input, as standard, 3 decimals for the cost price in VM!)

But if you must - and want to do the VAT reduction to 3DP - here you go:

update yourprefix_virtuemart_product_prices as p

SET p.product_price = round(p.product_price /1.15,3)

Regards
A

Joomla 3.10.11
php 8.0


dylanphelan

Quote from: AH on December 31, 2021, 15:24:21 PM
The point was that SQL lends itself to these very simple (one off) tasks.

It would have been easier for the user if you had said what your concerns were for rounding.  However rounding will occur in VM at some point and there is a real ex-VAt price that will be to 2 decimals for most businesses.

So it can be achieved using this simple SQL, if you prefer a possible 3 decimals for your ex-VAt price (My advice is don't input, as standard, 3 decimals for the cost price in VM!)

But if you must - and want to do the VAT reduction to 3DP - here you go:

update yourprefix_virtuemart_product_prices as p

SET p.product_price = round(p.product_price /1.15,3)



Thanks. This seems like an easy way to do it.

I already purchased  CSVI sub before I saw these posts.
I have used CSVI, but it's a bit of a hassle getting it set up and working.

I may very well go the SQL route.

dylanphelan

Quote from: AH on December 31, 2021, 10:53:37 AM
Just do a simple SQL command in phpmyadmin

to reduce the price to the ex vat element

update yourprefix_virtuemart_product_prices as p

SET p.product_price = round(p.product_price /1.15,2)



price update done. Thanks - that was so easy!

AH

Glad to be of help - thanks for the update.
Regards
A

Joomla 3.10.11
php 8.0