Hey guys.

This thread will be for stuff about price tracking in the database.

Right now I am simply going down the marketplace threads and putting in all the prices that I can in. Most are custom puzzle sales, which arent in the database yet (get working guys! ), so I am putting those in a spreadsheet.

I am, of course, scouring ebay as well for rare puzzle sales.

Hopefully in the near future, those average prices will be more accurate (and visible to everyone except katsmom )

LOL I am glad this point is emphasized!




Pantazis

One thing that will help me:

After an auction ends, please don't delete the auction link. I still need to look at it to get information.

The attached Excel 2003 spreadsheet will be useful for performing mass imports. If you are mass-importing historical data please use it instead of any custom spreadsheet you may have already built. If you are entering items one at a time as you browse through eBay, plaese do that using the online forms, not the spreadsheet. I ask this because anything entered into a spreadsheet runs the risks of being duplicated by someone else. Also, actually importing the spreadsheet requires my time and effort, and one big advantage of enlisting help with generating and maintaining the site content is to allow me to focus on other aspects of the site development.

Here's a brief explanation of the columns:

PuzzlePriceKey: Just ignore it. I'll need it to perform the import.

PuzzleKey: Each item in the puzzle database has this unique numeric key. You can find the key by browsing to the puzzle's page (example: Rainbow Cube). Look for the characters "pkey=" in the URL of the page (visible in your browser's address bar). The set of 1 to 4 numbers after the equals sign is the PuzzleKey. In the case of the Rainbow Cube, it's 35.

Sale Date: In the standard SQL date format: yyyy-mm-dd. Today's date, March 28 2009, would be entered as "2009-03-28".

SaleType: Select one of the options in the drop down. It's possible to add new options, but we should talk about it in the forum first.

AuctionItemNumber: If it's an eBay auction, enter eBay's item number here. This enables us to create a link to the auction page. The eBay item number can easily be found at the top of the eBay auction page, to the right of the auction name. For example, the eBay item number for this auction is 120389169908.

PuzzleCondition: Select one of the options in the drop down. It's possible to add new options, but we should talk about it in the forum first.

PackageCondition: Select one of the options in the drop down. It's possible to add new options, but we should talk about it in the forum first. I used "N/A" to mean things like "this item doesn't come in a package" for books, custom puzzles, or similar.

ItemNotes: Notes of anything (other than the puzzle or package condition) that might have dramatically affected the sale price.

Contributor: Your name.

Sandy

Oh, something else: auctions that have more than one item in them. Unless one of the items clearly amounts for almost the entire selling price, skip it. Our goal should be to compile a practical resource of price trends for puzzles, not a history of every twisty puzzle sale. For example: don't include an auction that has a 2x2x3 and a 2x3x3 together since we can't tell how much each of them sold for. But if a 2x2x3 is being sold with a dollar store 3x3x3 sticker variation, we can quite safely include the selling price in the 2x2x3 data and include a note that indicate the presence of the additional item.

Sandy

Sandy- The main reason I am using excel and not entering them straight in the database is because 90% of the prices I'm doing are for puzzles not yet in the database. Any prices for puzzles in the database that I find go straight into the database.

EDIT: 2 problems I'm having with the spreadsheet:

When I try to enter the date, it always changes is into yyyy/mm/dd format. Can I change this? EDIT2: Fixed this.

When I paste in the auction number, it displays it as scientific notation, but when I click on it, in the function box, it still says the number in full. Is this okay?

The missing puzzles should be added to the puzzle database, of course. I guess we have a lot of catching up to do in that area.

With the date and auction number fields, you just have to change the cell format. Sounds like you sorted out the date format already. Highlight all the cells in the AuctionItemNumber column > right-click one of the cells in the column > choose Format Cells > choose the Number tab > set the Category to Number with 0 decimal places and no thousands separator. No more scientific notation.

Sandy

Thank you! It all works now.

Hi guys, just a question.

In my excel file, all the prices are without date and ebay auction number.
They only have the puzzle name and the final price.

Since auctions expire after 2-3 months, it was not efficient to keep
any extra information. Should I still enter them now?


Pantazis

Auction item number is not required, so no worries there. Set the SaleType to eBay, and just leave the auction number blank. As you pointed out, auction links don't work after three months anyhow (which is a bug in my code, since the old prices still link to auctions that have been deleted for years... another thing on the new To Do list).

Selling date is required. I think it's okay if you guess the closest month (May 2007: 2007-05-01) or year (2008: 2008-01-01) and include something like "Exact date unknown" in the notes for each one. What do others think? Does it matter that the date be accurate in a case like this?

Sandy

I can surely find the dates of those auctions I have bid on (they are all filed LOL).
For the rest, I *could* guess, especially as I remember some of them for interesting reasons.

It will just take more time for me to go through - the "Items you don't see everyday or that sold
for a high price" thread can also be used to crosscheck the dates, as a few of them *must* be listed there.
(many of which were posted by me anyway!)




Pantazis

PS. When not in US$, I had done direct conversion to $US at the *actual* time of the auction.
So I will use for all of them prices in US$.

> So I will use for all of them prices in US$.

Yes, please do. I forgot to mention that. USD is THE currency for the Price Tracker due to the dominance of eBay.com for we English speakers. If the sale was done in another currency, convert into USD using www.xe.com or similar, and round to the nearest cent.

Sandy

I like to have at least approximate dates. There *are* 'seasonal' fluctuations.

Still, as long as the accuracy range of the date is indicated, it's surely better to have *some* info than none at all?

I will try my best with the dates, but I disagree that even six months make such big difference.
In the finance markets, non-negligible changes for puzzle items, happen at periods *over* a year.
Still, I can guarantee, that in the worse time-accuracy cases, I will provide a correct date within a couple of months.

Moreover, do not be surprised if you see two auctions (with the same puzzle listed) with two completely
different prices. For example, I have seen Rotos being sold for a couple of dollars, as well as... $100!

Finally, while I was doing my Maths Master's Degree, I had a precision value data entry job, so you should
expect the exact accurate prices that I have on my personal sheet, to be in the forum's database.

Just give me some time, things are (still!) crazy here.




Pantazis

Hi guys,

I did some testing (added two test entries to Batman 2x2x2)
but it seems I cannot delete those fake entries now. Is there a way to do this?

Also, we need make sure we do not have a puzzle key number conflict.
For example, if I make a new entry with the Duff Beer, the number
should always remain the same. (The excel file seems to be the best way
and the dates will be spread using a nice distribution way to make things accurate enough).

Thanks,


Pantazis

I can do it. I haven't added a way for mods to delete online content (other than blanking it out, I guess). There is a code update planned that will allow for this. Someday.

I deleted the two testing prices I saw for the Batman 2x2x2, but left the one I saw for Bart Simpson Head... I assume it was valid.



I see what you mean. Since the price data can't be added until the puzzle is added to the museum, you need some way of matching which prices match up with which puzzles, and a way of keeping track of which prices are waiting for museum entries to be created. Rather than making up some arbitrary number (and then trying to remember that 2345 = "Duff Beer"), I suggest adding a field called Puzzle Name or Puzzle Desrciption for those puzzles that don't have official puzzle keys yet, and then ensuring that all prices for the same puzzle have the same Puzzle Name or Puzzle Description field value.

Thanks for continuing to work on this!

Sandy

Thanks Sandy, the Bart Simpson is indeed valid. The other two were supposed to go to the 2x2x2 Batman
figure (made by Warner Bros), it was a mix up. Still I wanted to test the way I could input.
it seems, using the Excel file is the best way.






Thanks Sandy, I hope I can finish it sooner than later. If not now, the time spent waiting at all the different
airports may prove useful LOL

So, to confirm: Is it ok to add a new field (puzzle name), input the prices, and then send the Excel file to you?
(If there is no key to be found for a new addition, I will leave it blank.)




Pantazis


PS. Then after completing this task, it will be easy to add new isolated cases.

If you are being held up by a puzzle not in the database, it makes sense to push that list my way (or the direction of one of the other moderators). We are behind, but it helps to be able to prioritize items when we get around to adding things.

Dave

ok, I think I leave will the puzzle-key entry blank for any new puzzle, and will let you do the rest.
Of course, if there are any questions, just let me know!




Pantazis