How to update cells from external data feed

asimps

New Member
Joined
Dec 9, 2008
Messages
3
I'm a very unsophisticated Excel user who needs help in figuring out how to update inventory spreadsheets from a supplier's website. I have Excel 2007 and Windows XP. I've never written a macro, or a query, and I'm not really sure what a pivot table is. So that tells you I'm not very knowledgeable about Excel, but I can follow instructions really well! I just need to know what to look for.

It's actually (I think) a really simple thing, but I don't know what to search for in the help database. So I'll describe what I'm doing and hopefully someone can point me in the right direction.

My inventory consists of between 5000-6000 items. There are quite a few columns for the SKU, title (which I custom make for each item I want to list on eBay and which I don't want to change once I've created it), wholesale price, and a lot of other things that don't change when the supplier updates their data. The only two things that can change are the SKU (newly added to db, deleted from db, or unchanged from last update) and the price (went up, went down, or gone if SKU deleted from db).

The supplier has data that I've figured how to import using the Data=>From Web, so it drops right into Excel in columns.

It looks sort of like this:

SKU Title Price
123456 item1 29.43
234567 item2 6.49
345678 item3 84.32

When I do an update, which I need to do daily because these are fast moving items this time of year, and I want to make sure my ebay listings are accurate, this is what it could contain:

SKU Title Price
123456 item1 27.42 (price dropped)
234567 item2 7.29 (price increased)
345678 item3 84.32 (out of stock, removed from db)
456789 item4 24.98 (new item added)

What is the best and simplest way to do this? I want to create a master db with ALL of the SKU's so then when items come back into stock I don't have to create a new custom title etc. I use the wholesale price data to calculate a selling price. Then I can import the subset of the db into my ebay store with only a minimum of cleanup required.

I now spend almost an entire day just prepping the data for import into ebay, and much of the steps are repeated every time I update the db. I'd rather spend time on getting more items listed by having a set of fixed data and only updating (automatically) the couple of things that have changed. Does this make sense?

Hopefully someone can help me out here. Thanks in advance! :)
 

dyaron

Active Member
Joined
Nov 26, 2007
Messages
291
Well, in order to compare the info, you'd need to setup two sheets. One with the previous day's data, and another with the current data you're updating. Then compare the values to determine if price dropped, removed from database, etc.

I'd probably start by creating a macro that copies the entire sheet as values into a new sheet. You could even track prices here with a trend perspective (each column representing a day). Take a look at the macro recorder, if you've never used it... it will open doors you didn't know existed.
 

asimps

New Member
Joined
Dec 9, 2008
Messages
3
Yes, that's exactly what I want to do. I never created a macro before... what I'm concerned about is the fact that the 2 spreadsheets will not have identical SKUs - some will have dropped off because the supplier is out of stock, others will be new adds that aren't on my spreadsheet, while the rest will be the the same. On top of this, there is the price variable interacting with the SKU: new SKU/new price, current SKU/different price, current SKU/same price. I want to be able to see what the activity was so that I can decide on whether to update my ebay listings.

If the SKU list was static, then I could easily do as you suggest, but it isn't.
 

asimps

New Member
Joined
Dec 9, 2008
Messages
3
Can anyone help me get started on this? As I said, I'm clueless except for really basic stuff. Would a macro do what I want it to do (see previous post)? How do I go about setting one up? I have no idea what a macro recorder is, and it didn't come up when I searched the Excel help files...

Thanks for any help. I'm under time pressure to get this thing working so anything anyone can do is appreciated!
 

Forum statistics

Threads
1,082,478
Messages
5,365,783
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top