Updating the selling price of a stock item is easy (Inventory > Items List > open the item > Selling Details tab > Base Selling Price). But if you have lots of stock items this might take a while. Instead, you can export your item list, update the selling prices in Excel, then import the updated information back into AccountRight.
Do you use item price levels or quantity breaks? See Bulk updating custom price levels and quantity breaks.
Don't use Excel?
You can modify the export file in any program that can open text files (like Notepad), but it's easier if you use a spreadsheet program.
Let's show you how to do this to update all item prices by 5%, but you can use the same approach for any price change.
If you need help using Excel, refer to the help in Excel.
Before you begin
- In AccountRight, go to File menu and choose Export Data then choose Items.
- Set the Export File Format field to Tab Delimited and set the First Record Is field to Header Record.
- Click Continue to display the Export Data information window.
- In the MYOB Fields list located in the right hand panel, click Item Number then click Selling Price. These fields will be added to the Export Fields list as shown here:
- Click Export.
- Specify where you want to save the export file then click Save. By default, the export file will be named ITEM.txt.
- Open Microsoft Excel.
- Go to File menu and choose Open. The Open window will be displayed.
- Set the Files of Type field to Text Files (*.prn *.txt; *.csv).
- Locate the ITEM.txt file you exported earlier, then click Open.
- If the Text Import Wizard is displayed, click Finish. The export file will be displayed as shown in the following example. Notice the first column is the Item Number and the second column is the Selling Price. Depending on your version of Excel, yours might look slightly different.
To make it easier to show in our example, we'll just update the selling price for 4 items.
- Select the price cells to be updated and move them across to the next column, as shown in the two windows below.
- Place your cursor in an empty cell and set its format to percentage.
- Enter the adjusting percentage in the cell as shown in the following example.
What percentage do I use?
For a price increase: 100% + percent increase, e.g. a 5% increase is 105%
For a price reduction: 100% - percent decrease, e.g. a 5% decrease is 95%
- Place your cursor in the selling price cell adjacent to the first item number. Press the = (equals) key on your keyboard.
- Place your cursor in the original selling price cell for the first item. Press the * (asterisk) key on your keyboard then place your cursor in the percentage cell. The formula bar in Excel will look similar to our example below, but referencing the cells you selected.
- Place your cursor in the Formula bar and enter the $ (dollar) symbol in front of the cell co-ordinates that reference the percentage cell, as per the example below.
- After inserting the $ symbols, click the 'green tick' just to the left of the formula and move your cursor to a blank cell. You should find the selling price for the first item is now updated to the new price. Make sure the new price is correct before applying the formula to the other items.
- Select the selling price cell that contains the formula. Move your cursor over the cell's border at its right hand lower corner. When your cursor turns into a + symbol hold down the left mouse button and drag it down over the blank selling price cells. The formula will now be applied to those cells and the new updated price for the respective items will be inserted as soon as you release the mouse button. The window below shows the updated prices.
- Go to the File menu and choose Save then click Yes to the file format message.
- Close Excel.
- In AccountRight, go to File menu and choose Import Data then click Items.
- In the Import File window, set the three fields as follows:
- Import Field Format: Tab Delimited
- First Record Is: Header Record
- Duplicate Records: Update Existing Records
- Click Continue to display the Open window.
- Navigate to the ITEM.txt file.
- Click the ITEM.txt file then click Open.
- When the Import Data window is displayed:
- Highlight Item Number in the Import Fields panel then click Item Number in the MYOB Fields list in the right hand panel.
- Highlight the Selling Price in the Import Fields panel then click Selling Price in the MYOB Fields list.
The Import Data window will look similar to the example below. Please note that the import process will ignore the '105%'.
- Click Import.
- Read the import message that is displayed and note any errors or warnings then click OK.
Errors and warnings can be checked by opening the MYOBLOG.txt file. See the AccountRight help for more information.
- Go to the Inventory command centre and click Items List and confirm your item prices have been updated correctly.