ANSWER ID:13272

For various economic reasons you may decide to update the current selling price of your items by a given percentage amount.  If you have a large Items List, the task of updating each individual item's price could be quite time consuming.  This support note explains how in several steps you can use your software's export / import functionality and Microsoft Excel to quickly and easily update all your item prices.

Note: You may be using a different version of Excel than the one used in this support note, but the procedure will be the same.

Before you begin

• An overview of the software's export/import functionality can be found in the Online Help (just press F1 on your keyboard when using your MYOB software). Having trouble? Try our support note Importing and exporting data.
  
• We strongly recommend you backup your company file before making any changes to it. For more information on this, see our support notes , , and .
  
• This support note gives the basic steps on using Microsoft Excel to update your item prices.  Please understand that beyond this support note, MYOB Technical Support is unable to assist you with Excel functionality.  Information on using Excel can be found in the Excel Help files.
  
• The steps with regards to Excel in this support note may vary slightly from the version of Excel installed on your computer.
  
• If you are using multiple price levels for your items, see this section of the support note.

What are the tasks for updating item selling prices by a given percentage?

An example of this would be where you decide to increase the selling price of all your items, or a large portion of them, by a given percentage.  The four basic tasks to do this are:

1. Export your items.
   
2. Open the exported items information in Excel.
   
3. Create and apply a formula to update the item prices.
   
4. Import the items back into your company file.

Task 1 - Export your items

If using the new AccountRight (v2011 or later)

1. In your AccountRight company file, go to the File menu and choose Import/Export Assistant. The Import and Export Assistant appears.
   
2. Select Export data then click Next.
   
3. In the Export field, select the type of data being exported. In our example this would be Items.
   
4. Click Next.
   
5. At the Format step, leave the default format options and click Next.
   
6. At the Select Fields step, the fields in the left panel show the fields that are available for export. To choose a field to be exported, click a field in the left panel then click Add. The field will be added to the list of fields to be exported in the panel on the right.
   Select and add the fields Item Number and Selling Price.
   
   
7. After selecting the above fields and they are both listed in the panel on the right, click Export. The Save As window appears.
   
8. Specify where you want to save the file. To keep it simple, save the file to your Desktop and leave the default file name (ITEM.TXT).
   
9. Click Save. The export file will be saved in the specified location.
   
10. Click Close.

If using a different version

1. In your MYOB software, go to File menu and choose Export Data then choose Items.
   
2. Set the Export File Format field to Tab Delimited and set the First Record Is field to Header Record.
   
3. Click Continue to display the Export Data information window.
   
4. In the MYOB Fields list located in the right hand panel, identify the two fields - Item Number and Selling Price (you will need to scroll down to access Selling Price).  Click on Item Number then click on Selling Price.
   Your Export Data window should now look like the following example.
   
   
   
5. Click Export then follow the on-screen steps to save the export text file.  By default, the software will name the export file ITEM.txt.

Task 2 - Open ITEM.txt file using Excel

1. Open Microsoft Excel.
   
2. Go to File menu and choose Open. The Open window will be displayed.
   
3. Set the Files of Type field to Text Files (*.prn *.txt; *.csv).
   
4. Locate the ITEM.txt file you exported earlier, then click Open.
   
5. If the Text Import Wizard is displayed, click Finish. The export file will be displayed as shown in the following example. Depending on your version of Excel, yours might look slightly different.
   

Task 3 - Create and apply a formula to update the item selling prices

Note: For display purposes, in our example we will update the Selling Price for items 100, 110, 120 and 123.

1. Select the price cells to be updated and move them across to the next column, as shown in the two windows below.
   
   
   
2. Place your cursor in an empty cell and set its format to percentage.
   
3. Enter the adjusting percentage in the cell as shown in the following example.
   
   
   
   Note: To increase the item prices by 5%, we need to use 105%.  The formula to determine the percentage amount is:
   Price increase: 100% + percent increase
   
   Price reduction: 100% - percent decrease
   
   
   
4. Place your cursor in the Selling Price cell adjacent to the first Item Number.  Press the "=" (equals) key on your keyboard.
   
5. 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.
   
   
   
6. 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.
   
   
   
7. 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.
   
8. 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.
   
   
   
9. Go to the File menu and choose Save then click Yes to the file format message.
   
10. Close Excel.

Task 4 - Import the updated prices into your software

Note: Make sure you make a backup of your company file before importing the updated prices.

If using the new AccountRight (v2011 or later)

1. In your AccountRight company file, go to the File menu and choose Import/Export Assistant. The Import and Export Assistant appears.
   
2. Select Import data then click Next.
   
3. In the Import field, select the type of data being imported. In our example this would be Items.
   
4. Click Browse and locate the file to import. This will be the file you exported and modified in Excel. In our example, this would be the ITEM.TXT file.
   
5. Click the file then click Open.
   
6. Click Next.
   
7. At the Format step, select the following options:
   

1. Click Next.
   
2. At the Match Fields step, match the fields to be imported against the fields in your company file. To do this, click the Item Number field in the Import Fields panel (on the left), then click the Item Number field in the Available fields panel (on the right). Repeat this to match the Selling Price field in the Import fields panel to the Selling Price field in your company file.
   
   
3. Click Next.
   
4. At the following step it is recommended to click Back up and take a backup of your company file.
   
5. When ready to import your updated data, click Import. Your updated data will be imported.
   
6. At the Conclusion step, check that all data was imported successfully. You can also check the import log for details of errors or warnings.

If using a different version

1. In your MYOB software, go to File menu and choose Import Data then click Items.
   
2. In the Import File window, set the three fields as follows:
   

1. Click Continue to display the Open window.
   
2. Navigate to the folder containing the ITEM.txt file.
   
   
3. Click to highlight the ITEM.txt file then click Open.
   
4. 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. Do the same for the Selling Price, matching it to the 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%'.
   
   
   
5. Click Import.
   
6. Read the import message that is displayed and note any errors or warnings then click OK.
   
   Notes:
   
   
   • Depending on which software version you are using, you may receive some warnings. These errors and warnings can be checked by opening the MYOBLOG.txt file.  For more information, see our support note .
     
   • It's likely the warning below will occur. This won't affect your data or the import process and can be ignored.
     'Warning 604: Standard cost field is blank, cost reset to $0.00.'
     
7. Go to the Inventory command centre and click Items List and confirm your item prices have been updated correctly.

Can this process be used to update item pricing levels?

By following the steps within this support note, you will have updated the items' Base Selling Price. If you are using a version of MYOB software which allows item pricing levels to be used, you can update each item price level by applying the same principles as those described above. In other words, export the price level fields, update the pricing levels in Excel, and then import the updated information into your company file. For more detailed information see our support note Updating the selling price matrix using Excel.

Having issues importing data?

See our support note .