How to Add categories to stock list in Excel

  • 1
  • Question
  • Updated 6 years ago
  • Answered
  • (Edited)
Hi I have been using Quickbooks and POS for about 9 years we have now reached the 14500 stock limit and my book keeper also said she feels the file is corrupted as there a few little glitches that have been becoming more frequent.

So I am starting with a new version of Reckon Plus and POS lite. We will export the stock list to excel and edit out old items etc and then import.

My question is, all the items in our inventory are not categorised we are a jewellery store so would like to split the items into categories for better reporting. When the file is in excel how do I go about adding categories, say gold jewellery, silver jewellery, giftware and watches for example. Any help on this would be greatly appreciated.
Photo of Rick Vela

Rick Vela

  • 440 Points 250 badge 2x thumb

Posted 6 years ago

  • 1
Photo of Mirko

Mirko, Alum

  • 34,812 Points 20k badge 2x thumb
Hey Rick,

It may be best to consult a Reckon Accredited Partner regarding set up on stock lists and optimising your reporting.

http://home.reckon.com.au/support/Home/ReckonSupport/FindAReckonAccreditedPartner.aspx

There are also partners on the Community who may approach you, or you are able to contact them.

Thanks,
Mirko

Photo of Rick Vela

Rick Vela

  • 440 Points 250 badge 2x thumb
Great idea Mirko, I used an "accredited partner" many years ago when I set up the software, lets just say I had more of an idea on what needed to be done then he did. As I am sure you are aware there is a great deal of difference between the abilities of partners ..... once bitten twice shy.
(Edited)
Photo of Nathan Elcoate

Nathan Elcoate, Accredited Partner

  • 7,354 Points 5k badge 2x thumb
Hi Rick, This certainly can be done, however it may be easier for you to setup your Category Items in your Item List first. So go and setup some Inventory Items called say gold jewellery, silver jewellery, giftware and watches for example. Then go into one or two items, and you will be able to make your existing items as sub-items of the category items you just created. In the export files these will appear as, for example giftware:creamics or watches:seiko - where the Colon is the seperator that defines one item is a sub-item of another.
Photo of Nathan Elcoate

Nathan Elcoate, Accredited Partner

  • 7,354 Points 5k badge 2x thumb
So then all you need to do in your Excel file, is create a column of all of the header names and what I do is Concatenate the headers to the existing item names. So create one column with the header names, then one column with all : colons, then your item list. A simply concatenate will join all 3 together. This then gets pasted into the Item Name column into your Reckon stock export file, and when re-imported, will all import under the correct category headers. I hope this makes sense, if not, just drop a line back on here.
Photo of Rick Vela

Rick Vela

  • 440 Points 250 badge 2x thumb
Hi Nathan you are a champion, thanks for taking the time to reply I will have a look on the software tomorrow to better understand what you are suggesting. Thanks again.
Photo of Jason Hollis

Jason Hollis, Head of Product

  • 10,302 Points 10k badge 2x thumb
Sorry to hear you had a bad experience previously Rick. Inventory (new data file) set-ups are definitely in the advanced category of Reckon knowledge, so be sure to ask the right questions of an AP if you need help.

Another consideration is whether you want to import not only the QTY but the VALUE of your inventory, which is a little more complex than importing just the item list, but nonetheless quite achievable, and a massive time saver.

Again, if you need help from an AP, please ask them if they have done this before, and to explain to you the methodology behind it. You'll soon know if they are competent in this area. As you rightly say, all partners have different skill sets and abilities, so you just need to find one who specialises in inventory.



Regards,
Jason
Photo of Rick Vela

Rick Vela

  • 440 Points 250 badge 2x thumb
Hello guys, I just exported my stock list to excel to have a look, all I need to do to edit out unwanted stock items is to delete that whole line?, I then can save the edited spreadsheet as an iif file and import straight back into my new version of quickbooks and fresh company file quickbooks. Have I got this correct? Thanks again.
Photo of Nathan Elcoate

Nathan Elcoate, Accredited Partner

  • 7,354 Points 5k badge 2x thumb
Hey Rick, you would be better off going to File - Utilities - Export file to iif - the export out only the items list and save it. You can then open the iif file with Excel, edit that file as you wish and delete out your lines then reimport.
Photo of Rick Vela

Rick Vela

  • 440 Points 250 badge 2x thumb
That is exactly what I did Nathan. Cheers
Photo of Kylie Smyth

Kylie Smyth

  • 234 Points 100 badge 2x thumb
Rick can you give me detailed instructions on how you imported back to Quickbooks? im trying to do this exact same thing but it refuses to save as an IIF file in excel..instead it is automatically saving as a txt file and if i change it, it wont import back into QB...im wondering if its because QB premier 2013 isnt compatible with wind 8 and excel 2013...and like you...have had very bad experience with AP from Reckon..paying them for things i knew more about..total waste of time. i have never had this problem before until now...typical right when reckon  subscription is due...Reckons solution for me is to upgrade to Enterprise after 10yrs of loyal following, this is now my huge problem.

Photo of Jason Hollis

Jason Hollis, Head of Product

  • 10,302 Points 10k badge 2x thumb
Kylie, your issue is more to do with excel, not Reckon. An IIF will definitely save as an IIF on Win 8 Office 2013 etc...

Also, from your other post, to rename the file extension to IIF you need to ensure you can see file extensions of known file types in Windows, and that you are viewing the file Detail list, not just list....

I know you are frustrated, and I would be too, but it will work. 

ps. you can also import a *.txt file via IIF without changing the file extension, so there sounds like there's more to it re the corruption etc...
Photo of Kylie Smyth

Kylie Smyth

  • 234 Points 100 badge 2x thumb
it will save Jason your right, but as soon as i edit it it wont save as the original file ext...do you know of any AP in Perth WA who have any idea what they are doing in regards to this?? each one ive come across has had no idea so fingers crossed you may do?

Photo of Jason Hollis

Jason Hollis, Head of Product

  • 10,302 Points 10k badge 2x thumb
Don't import the quantities though, or you'll have quantities with no value. The excel import will give you qty and value, but will be missing other fields, so it's an export to iif, fix and reimport process - not to mention the little gotchas.

At least do a FULL VERIFY backup of the new data file before you import anything.
Photo of Nathan Elcoate

Nathan Elcoate, Accredited Partner

  • 7,354 Points 5k badge 2x thumb
Just to elaborate, once you import as per Jason's advice on the Qty's - you will have to immediately go and do a Stock Adjustment to enter all of your stock in (with values!), so that Reckon has a VALUE on the stock which is just as, if not more, important than the Qty's. If your stock has no value, it won't write anything to Cost of Goods Sold when you start invoicing out. Glad to be teaming up with Jason to assist you here Rick.
Photo of Jason Hollis

Jason Hollis, Head of Product

  • 10,302 Points 10k badge 2x thumb
And just to elaborate on my learned colleagues comments Rick, you can bring the full inventory value in from the excel import function to begin with - way way easier than 10,000+ stock adjustments.  

If you have gone too far for that, another trick is to do your adjustments via a 'zero sell' sales receipt and then journal from COGS to opening balance etc...

Depending on your setup, sometimes it's easier to type the item name in the sales receipt (especially if your counts were not done in item list order) than it is to use the stock adjustment module.

Over to you Nath...
Photo of Nathan Elcoate

Nathan Elcoate, Accredited Partner

  • 7,354 Points 5k badge 2x thumb
Go for it Jase :-)
Photo of Jason Hollis

Jason Hollis, Head of Product

  • 10,302 Points 10k badge 2x thumb
You know me so well Rick... :)

If you import a qty with no value, your inventory valuation report will say something like 10 widgets on hand, value $0.00

If you buy 10 more in @ $10 each, it will say 20 on hand at $100 value, which is effectively $5 avg cost. This is incorrect, as they should be $10 avg cost.

The reason I say import 0 qty in the IIF is so that you can do an inventory adjustment and enter the correct QTY and COST (hence value).

There are other adj options, but lets not confuse the issue. Just bring in the item list, qty = 0, and then use any method you like to update the stock on hand (eg. inventory adjustment).

I certainly have a headache now Rick, but hopefully that made sense??
Photo of Rick Vela

Rick Vela

  • 440 Points 250 badge 2x thumb
Yes champ it has made sense, so do I delete the text in each of the qty column and leave blank or change them all to zero?

If I was to go through and delete my unwanted line items from the file and then email you the excel file would you be able to work your magic, so that I could import the file complete as you said above with qty etc all there? Or is this still not able to be done?

Shoot me an email at rick@laidleyjewellers.com.au and we can talk more about it.

Also someone asked the below question further up in the posts .... maybe you can answer for Her.....


Rick can you give me detailed instructions on how you imported back to Quickbooks? im trying to do this exact same thing but it refuses to save as an IIF file in excel..instead it is automatically saving as a txt file and if i change it, it wont import back into QB...im wondering if its because QB premier 2013 isnt compatible with wind 8 and excel 2013...and like you...have had very bad experience with AP from Reckon..paying them for things i knew more about..total waste of time. i have never had this problem before until now...typical right when reckon  subscription is due...Reckons solution for me is to upgrade to Enterprise after 10yrs of loyal following, this is now my huge problem.


Photo of Jason Hollis

Jason Hollis, Head of Product

  • 10,302 Points 10k badge 2x thumb
1. make it 0 - Reckon has a habit of replacing NULL with 1
2. i'll see how far you have gone, but could possibly be ok. I'll shoot you an email.

ps. you still need a record of the actual qty for the initial import. Let's just talk through the options offline.


Jason
(Edited)
Photo of Graham Boast

Graham Boast, Accredited Partner

  • 11,764 Points 10k badge 2x thumb
Hi Rick

The import process will totally ignore the Qnty and Totvalue columns, so you are safe to leave them as is.

Importing these values into Reckon is a bit of a process. By far, the best way is to use the File > Utilities > Import > Excel File method.  This will bring in the Qnty and Totvalue columns.  You will need to create an extra column "As Of Date" with value 06/30/2014 (30th June in mmddyyy format).

BUT.....
  • this process will only work for items not already in the file - you can't update the stock on hand values for items that are already in the system;
  • it can't bring in barcodes, so they have to be imported via iif file
  • often, you need the new file to be set up before the stock figures are available
I tend to import Stock on Hand as a transaction, usually a Bill or a Cheque.  From the existing iif export, I will create an excel spreadsheet with the full item name, description, Qnty, average cost ex GST and total value (Qnty x average cost = total value), then get the client to update this with stocktake figures.  I can then manipulate this data to create an iif import file that will import a cheque (from a dummy cheque account) that will update the stock values.  The advantage of this is that it can be done after the 1st July!  Its a few hours work for me but it is much faster, cheaper and accurate than data entry.

The other issue that you raised regarding the iif files saving as text, I have covered in the original post here:
https://community.reckon.com.au/reckon/topics/importing_item_list_from_old_company_to_new

Graham
graham@reckonhelp.com.au