Online since 2002. Over 3300 puzzles, 2600 worldwide members, and 270,000 messages.

TwistyPuzzles.com Forum

It is currently Sun Apr 20, 2014 6:49 am

All times are UTC - 5 hours



Post new topic Reply to topic  [ 20 posts ] 
Author Message
 Post subject: Price Tracking
PostPosted: Sat Mar 28, 2009 10:38 am 
Offline
User avatar

Joined: Mon Aug 11, 2008 7:19 pm
Location: Green Bay, Wisconsin
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! :P ), 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: )


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 11:22 am 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
ejisfun wrote:
(and visible to everyone except katsmom :lol: )


LOL I am glad this point is emphasized!

:lol: :lol: :lol: :lol: :lol: :lol: :lol:


Pantazis

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 2:03 pm 
Offline
User avatar

Joined: Mon Aug 11, 2008 7:19 pm
Location: Green Bay, Wisconsin
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.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 2:44 pm 
Offline
User avatar

Joined: Thu Jan 24, 2002 1:10 am
Location: Toronto, Canada
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


Attachments:
File comment: An Excel 2003 spreadsheet for performing mass imports of price tracking data.
Price Tracking Data Mass Import.xls [80.5 KiB]
Downloaded 135 times
Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 2:49 pm 
Offline
User avatar

Joined: Thu Jan 24, 2002 1:10 am
Location: Toronto, Canada
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


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 8:32 pm 
Offline
User avatar

Joined: Mon Aug 11, 2008 7:19 pm
Location: Green Bay, Wisconsin
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?


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 8:52 pm 
Offline
User avatar

Joined: Thu Jan 24, 2002 1:10 am
Location: Toronto, Canada
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


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sat Mar 28, 2009 9:03 pm 
Offline
User avatar

Joined: Mon Aug 11, 2008 7:19 pm
Location: Green Bay, Wisconsin
Thank you! It all works now. :D


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sun Mar 29, 2009 1:12 am 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
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

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sun Mar 29, 2009 10:15 am 
Offline
User avatar

Joined: Thu Jan 24, 2002 1:10 am
Location: Toronto, Canada
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


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sun Mar 29, 2009 10:48 am 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
Sandy wrote:
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$.

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sun Mar 29, 2009 11:11 am 
Offline
User avatar

Joined: Thu Jan 24, 2002 1:10 am
Location: Toronto, Canada
> 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


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sun Mar 29, 2009 11:27 am 
Offline
User avatar

Joined: Wed Dec 29, 2004 7:57 am
Location: Στο Κάστρο του Αγίου Γεωργίου
Sandy wrote:

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 like to have at least approximate dates. There *are* 'seasonal' fluctuations.

_________________
...................Image..................
http://cube.helm.lu - http://gallery.helm.lu


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Sun Mar 29, 2009 12:28 pm 
Offline

Joined: Sun Dec 07, 2008 4:28 pm
Location: Utrecht, The Netherlands
Georges wrote:
Sandy wrote:

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


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Wed Apr 01, 2009 5:56 am 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
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

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Wed Jul 01, 2009 11:54 am 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
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

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Thu Jul 02, 2009 10:06 am 
Offline
User avatar

Joined: Thu Jan 24, 2002 1:10 am
Location: Toronto, Canada
kastellorizo wrote:
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?


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.

kastellorizo wrote:
Also, we need make sure we do not have a puzzle key number conflict.


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


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Thu Jul 02, 2009 12:20 pm 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
Sandy wrote:
kastellorizo wrote:
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?


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.


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.



Sandy wrote:
kastellorizo wrote:
Also, we need make sure we do not have a puzzle key number conflict.


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

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Mon Jul 06, 2009 1:06 pm 
Offline
User avatar

Joined: Thu Dec 21, 2006 5:32 pm
Location: Bay Area, CA
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

_________________
Image
LitwinPuzzles.com has info on my puzzles.


Top
 Profile  
 
 Post subject: Re: Price Tracking
PostPosted: Mon Jul 06, 2009 1:12 pm 
Offline
User avatar

Joined: Fri Nov 04, 2005 12:31 am
Location: Greece, Australia, Thailand, India, Singapore.
DLitwin wrote:
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

_________________

Educational R&D, Gravity, 4D Symmetry, Puzzle Ninja, Matrix Mech, Alien Technology.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 20 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Forum powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group