Author Topic: Bulk update pricing  (Read 2083 times)

dylanphelan

  • Jr. Member
  • **
  • Posts: 138
Bulk update pricing
« on: December 30, 2021, 19:57:02 pm »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4668
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Bulk update pricing
« Reply #1 on: December 31, 2021, 02:36:56 am »
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

  • Jr. Member
  • **
  • Posts: 138
Re: Bulk update pricing
« Reply #2 on: December 31, 2021, 09:03:23 am »
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3510
  • VirtueMart Version: 3.8.9
Re: Bulk update pricing
« Reply #3 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

Code: [Select]
update yourprefix_virtuemart_product_prices as p

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

Joomla 3.9.27
php 7.4

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4668
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Bulk update pricing
« Reply #4 on: December 31, 2021, 11:13:27 am »
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3510
  • VirtueMart Version: 3.8.9
Re: Bulk update pricing
« Reply #5 on: December 31, 2021, 12:00:24 pm »
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.9.27
php 7.4

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4668
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Bulk update pricing
« Reply #6 on: December 31, 2021, 12:56:51 pm »
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3510
  • VirtueMart Version: 3.8.9
Re: Bulk update pricing
« Reply #7 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:
Code: [Select]
update yourprefix_virtuemart_product_prices as p

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

Joomla 3.9.27
php 7.4

Maxim Pishnyak

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2656
You can support Community by voting for Project on the JED
https://extensions.joomla.org/extension/virtuemart/#reviews
Join us at
https://twitter.com/virtuemart

dylanphelan

  • Jr. Member
  • **
  • Posts: 138
Re: Bulk update pricing
« Reply #9 on: January 06, 2022, 18:18:53 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:
Code: [Select]
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

  • Jr. Member
  • **
  • Posts: 138
Re: Bulk update pricing
« Reply #10 on: January 06, 2022, 21:28:44 pm »
Just do a simple SQL command in phpmyadmin

to reduce the price to the ex vat element

Code: [Select]
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3510
  • VirtueMart Version: 3.8.9
Re: Bulk update pricing
« Reply #11 on: January 09, 2022, 15:56:58 pm »
Glad to be of help - thanks for the update.
Regards
A

Joomla 3.9.27
php 7.4