nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to Add categories to stock list in Excel

Rick Vela

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.

Comments

Mirko

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

Nathan Elcoate

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.

Nathan Elcoate

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.

Rick Vela

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.

Rick Vela

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.

Jason Hollis

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

Rick Vela

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.

Nathan Elcoate

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.

Jason Hollis

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.

Nathan Elcoate

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.

Jason Hollis

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...

Rick Vela

That is exactly what I did Nathan. Cheers

Rick Vela

Jason, we were going to do the stocktake in the excel spreadsheet, that would allow us to update stock values and to remove any items that are no longer in stock, thus getting around the 14500 line items problem. We would then import all current items which would include current quantity on hand etc. Does this sound correct?

Jason Hollis

You need to import via the excel import function as IIF will only bring in qty's, not value. eg 50 widgets x $2.00 cost = $100 asset value.

Excel doesn't have all of the required fields though, so it's another export to IIF, complete missing data with a vlookup then import again.

Doesn't take that long if you know what you are doing. The other options are to enter QTY / VALUES via inventory adjustments / sales receipt method etc.. to get the same result. Pretty time consuming though.

Rick Vela

I just thought of another thing .... maybe this is what Nathan was talking about, I will have to go through and also edit the actual stock value as well, if its showing currently 1 item a cost of $20 a retail of $60 and I find 2 of those items I will also have to edit the the column L which is the value column to reflect the updated stock. Man what a job. Am I looking at doing this the right way or do you guys have an easier solution say stocktaking in reckon - exporting -removing 0 quantity line items and then importing.

Jason Hollis

The value in the IIF will not import Rick, but you use this to check the Average cost - that is what the inventory should import in at, NOT your last buy price.

As I said originally, this is a specialised area of the software. Flick me your email offline and I can see who is in your area that may be able to help.

You can send it through at www.cutcher.com.au/services/software/contact

Rick Vela

Jason I appreciate what you are saying I may get someone in once the editing is done. As I said I will be the one doing the leg work editing the information, there is a lot that needs to be edited I can't afford to pay someone $200 an hour to sort through 14 500 lines of stock that's something I or one of my staff can do.

So when the import is done the column L which is the value column is not imported?. I would have thought that everything that was exported as an IIF file would in turn be imported if it was an IIF file. What else is left off when you import that was originally exported?

Nathan Elcoate

Only the values do not import

Jason Hollis

As Nath says, everything else is ok - value is a calculated field, hence why it won't import. There are also some other tricks with barcode formats etc...

But before you get a consultant involved just worry about clearing the rows, not the columns. You are the owner of the data and will know what needs clearing, so you don't use a consultant for that stuff.

A tip is to make the item inactive in Reckon and then just data filter on that column in the IIF and delete (careful with the category headers though).

Nathan Elcoate

We usually journal the stock value in when we bring in the Trial Balance, which makes the Balance Sheet correct, your stock Qty will be there with all of the correct information, however the stock won't have a value, which means when you sell the stock, nothing will get written to Cost Of Sales. So the only "official" way to write the Stock Value in per item, is with a Stock Adjustment or when you enter a Bill for the item. However, there are also Jason's Excel methods :-)

Rick Vela

So I have a bookeeper that will handle all of the accounting side of things, I just need to ensure the item list is correct.

All columns are imported except for the value column..... correct?

The fact the value column is not imported is a problem.... yes?

What is the easiest way to then fix this problem, I have two guys offering two different solutions, which is the easiest?

Jason Hollis

Rick, just do this...

1. Import all EXCEPT value and qty (make qty zero)
2. Adjust your stock on hand however you like
3. Review stock value report (NEW FILE vs OLD) and get your bookkeeper to adjust the balance sheet where necessary as I doubt you'll import the correct average cost 'exactly'.

Rick Vela

Jason and Nathan I appreciate you taking the time to run me through this , it all makes sense except the import the qty as zero, whats the problem with importing the updated qty's. You said before it will import the qty's just not the sum value of qty x cost price.

I know you are sitting behind a screen maybe wanting to punch it right now, bare with me I am nearly there.

Nathan Elcoate

Go for it Jase :-)

Kylie Smyth

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.

Jason Hollis

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??

Rick Vela

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.

Jason Hollis

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...

Jason Hollis

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

Kylie Smyth

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?

Graham Boast

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/discussion/6164073/importing-item-list-from-old-company-to-new

Graham

graham@reckonhelp.com.au