Valiant Collection points question...

Discuss the VALIANT comics, characters, and collecting.
PLEASE DO NOT REVEAL SPOILER INFORMATION IN YOUR TOPIC TITLE.

Moderators: greg, Daniel Jackson

User avatar
etos45
I live to be inefficient!
I live to be inefficient!
Posts: 4141
Joined: Mon Sep 27, 2004 8:41:32 pm
Location: Anywhere but here... nope, there I am.
Valiant Collection points question...

Post by etos45 »

This probably goes out to Greg, but maybe someone else can answer too...

Okay, so I was doing the Valiant inventory thing on the web site the other day when I got the generated text thing to email to Greg. I didn't think much about it and just emailed it away. Later it occurred to me that this was probably being run through excel (because of the separating commas) which just happened to be a lot like the excel list I was trying to make. My question is how would I go about generating some of the stats like what's on people's inventory list? I made one for total number of issues, but I can't figure out how to come up with a number for individual issues, total percent complete and all that. I'd also love it if I could have some of the other separating factors like on the website. For example, how many variants, actually calculating the points, etc. Basically, I want whatever Greg (or whoever does it) uses, if possible.

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory. :|

User avatar
betterthanezra
Wanna see an unpublished Shadowman page?
Wanna see an unpublished Shadowman page?
Posts: 12179
Joined: Mon Feb 09, 2004 12:18:28 am
Valiant fan since: 1991
Favorite writer: Josh Dysart
Location: Scoot over, I have to get in behind you.

Post by betterthanezra »

greg wrote:Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory. :|
YA Think 8-)

-Brian

acc205
Harbinger rank: Aonisaibushi
Harbinger rank: Aonisaibushi
Posts: 189
Joined: Wed Sep 29, 2004 3:57:22 am
Location: downtown

Post by acc205 »

Someone needs an intern....or a knob :lol:

User avatar
etos45
I live to be inefficient!
I live to be inefficient!
Posts: 4141
Joined: Mon Sep 27, 2004 8:41:32 pm
Location: Anywhere but here... nope, there I am.

Post by etos45 »

greg wrote:Close... the comma-delimited style is used by Access.
Argh!! Now I have to learn another program? :D
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! :P Not to mention it entered my Qty in for me.


greg wrote:I'm about a year behind on updating the inventory. :|


Heh... well, it's a fun system when it's up... I love playing the "points" game. :) Speaking of which, if you ever do get around to updating it and you want to delete G. Diehl, you can... I resubmitted the new one under etos45... figured it made more sense to use the screen name. :)


If any excel geniuses know how to get me where I wanna be I'd love to hear it... in the mean time I've got another 50 excel tutorials to watch... :roll:

User avatar
Chiclo
I'm Chiclo. My strong Dongs paid off well.
I'm Chiclo.  My strong Dongs paid off well.
Posts: 20578
Joined: Tue Oct 03, 2006 1:09:11 am
Favorite character: Kris
Location: Texas
Contact:

Post by Chiclo »

etos45 wrote:
greg wrote:Close... the comma-delimited style is used by Access.
Argh!! Now I have to learn another program? :D
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! :P Not to mention it entered my Qty in for me.
Sorta, not really. It's still all part of Office.

User avatar
etos45
I live to be inefficient!
I live to be inefficient!
Posts: 4141
Joined: Mon Sep 27, 2004 8:41:32 pm
Location: Anywhere but here... nope, there I am.

Post by etos45 »

Chiclo wrote:
etos45 wrote:
greg wrote:Close... the comma-delimited style is used by Access.
Argh!! Now I have to learn another program? :D
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! :P Not to mention it entered my Qty in for me.
Sorta, not really. It's still all part of Office.
Yeah... but... it's, like, the SOUTH side of Office... I only chill here on the North to North-East side of Office with Excel and Word. 8-)

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

betterthanezra wrote:
greg wrote:Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory. :|
YA Think 8-)

-Brian
Hey! :D

I just compiled all the data that's been waiting for the inventory update.

44,138 rows.

:atomic:

Ok, now to make it all the same format and actually update the database. :|

Some of you have "helpful" email systems that add extra line feeds for no reason...
It's especially lovely when you're trying to have one comic per row,
and you get data that looks like this:

1185,Unity Trade Paperback - Diamond Limited
1,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with
slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover
Price,1,204,"someone","8/4/2006"

...when it should look like this:

1185,Unity Trade Paperback - Diamond Limited,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover Price,1,204,"someone","8/4/2006"

Not a big deal, right? Well, not unless you've got 44,138 of them. :wink:

User avatar
whovianone
...it hurts so much ...my brain hurts!
...it hurts so much ...my brain hurts!
Posts: 3240
Joined: Fri Feb 02, 2007 10:15:18 am
Location: Kettering, OH

Post by whovianone »

greg wrote:
betterthanezra wrote:
greg wrote:Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory. :|
YA Think 8-)

-Brian
Hey! :D

I just compiled all the data that's been waiting for the inventory update.

44,138 rows.

:atomic:

Ok, now to make it all the same format and actually update the database. :|

Some of you have "helpful" email systems that add extra line feeds for no reason...
It's especially lovely when you're trying to have one comic per row,
and you get data that looks like this:

1185,Unity Trade Paperback - Diamond Limited
1,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with
slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover
Price,1,204,"someone","8/4/2006"

...when it should look like this:

1185,Unity Trade Paperback - Diamond Limited,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover Price,1,204,"someone","8/4/2006"

Not a big deal, right? Well, not unless you've got 44,138 of them. :wink:
OMG, I feel your pain Greg. I had to do an inventory for a furniture store. 126,000 entries! :o IN QUICKBOOKS!

Anyway, just know your efforts will be greatly appreciated by at least one of us.

:D :clap: :thumb:

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

Ok, forgot to add the link to the Acclaim totals earlier today:
http://www.valiantcomics.com/valiant/rescueacclaim.asp


Oh, and by the way... Inventories updated. :D

http://www.valiantcomics.com/valiant/co ... points.asp

User avatar
betterthanezra
Wanna see an unpublished Shadowman page?
Wanna see an unpublished Shadowman page?
Posts: 12179
Joined: Mon Feb 09, 2004 12:18:28 am
Valiant fan since: 1991
Favorite writer: Josh Dysart
Location: Scoot over, I have to get in behind you.

Post by betterthanezra »

greg wrote:Ok, forgot to add the link to the Acclaim totals earlier today:
http://www.valiantcomics.com/valiant/rescueacclaim.asp


Oh, and by the way... Inventories updated. :D

http://www.valiantcomics.com/valiant/co ... points.asp
:thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb: :thumb:

-Brian

User avatar
Chiclo
I'm Chiclo. My strong Dongs paid off well.
I'm Chiclo.  My strong Dongs paid off well.
Posts: 20578
Joined: Tue Oct 03, 2006 1:09:11 am
Favorite character: Kris
Location: Texas
Contact:

Post by Chiclo »

It's awesome you updated the inventories, but now I see I need to send an updated e-mail.

Woops. :oops:

User avatar
SmokinAFuente
Harbinger rank: Nensei
Harbinger rank: Nensei
Posts: 86
Joined: Fri Jan 26, 2007 4:41:49 pm
Valiant fan since: bloodshot #6 '93 or so..
Location: Martinsburg,WV

Post by SmokinAFuente »

Chiclo wrote:It's awesome you updated the inventories, but now I see I need to send an updated e-mail.

Woops. :oops:
Same here. :oops:

Now, when are you going to add the BloodShot Platinum to the list? :D

User avatar
SmokinAFuente
Harbinger rank: Nensei
Harbinger rank: Nensei
Posts: 86
Joined: Fri Jan 26, 2007 4:41:49 pm
Valiant fan since: bloodshot #6 '93 or so..
Location: Martinsburg,WV

Post by SmokinAFuente »

Thanks for doing all that work for us Greg. :thumb:

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

SmokinAFuente wrote:
Chiclo wrote:It's awesome you updated the inventories, but now I see I need to send an updated e-mail.

Woops. :oops:
Same here. :oops:

Now, when are you going to add the BloodShot Platinum to the list? :D
Actually, it can be added, but not using the automated tool.

If you've got one, just let me know. :thumb:

User avatar
superman-prime
scratch 1 for the coog guys
scratch 1 for the coog guys
Posts: 23252
Joined: Wed Mar 14, 2007 3:27:32 am
Location: phx az (east valley)

Post by superman-prime »

when was the last invantory update?

User avatar
superman-prime
scratch 1 for the coog guys
scratch 1 for the coog guys
Posts: 23252
Joined: Wed Mar 14, 2007 3:27:32 am
Location: phx az (east valley)

Post by superman-prime »

o wait :o :o :o :o :o :o :o :o :o im in there :thumb:
Greg is mah friend :thumb:


now i only got 2500 issues to add at this point
and 20 cgc issues :thumb:
(everyone wants a turok 9.8 :lol:

User avatar
whovianone
...it hurts so much ...my brain hurts!
...it hurts so much ...my brain hurts!
Posts: 3240
Joined: Fri Feb 02, 2007 10:15:18 am
Location: Kettering, OH

Post by whovianone »

superman-prime wrote:when was the last invantory update?
He just said he updated it, Supes. :thumb:

User avatar
whovianone
...it hurts so much ...my brain hurts!
...it hurts so much ...my brain hurts!
Posts: 3240
Joined: Fri Feb 02, 2007 10:15:18 am
Location: Kettering, OH

Post by whovianone »

superman-prime wrote:o wait :o :o :o :o :o :o :o :o :o im in there :thumb:
Greg is mah friend :thumb:


now i only got 2500 issues to add at this point
and 20 cgc issues :thumb:
(everyone wants a turok 9.8 :lol:
Ya know, I wouldn't pay for one, but I'd trade for one. PM me what you want. ;-)

User avatar
cjmiller2213
Harbinger rank: Aonisaibushi
Harbinger rank: Aonisaibushi
Posts: 197
Joined: Mon Mar 19, 2007 8:54:23 pm
Location: Indy

Post by cjmiller2213 »

etos45 wrote:
Chiclo wrote:
etos45 wrote:
greg wrote:Close... the comma-delimited style is used by Access.
Argh!! Now I have to learn another program? :D
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! :P Not to mention it entered my Qty in for me.
Sorta, not really. It's still all part of Office.
Yeah... but... it's, like, the SOUTH side of Office... I only chill here on the North to North-East side of Office with Excel and Word. 8-)
I am confused as to why you can't use this list in excel. I don't really know what you are talking about - but I do know if you have data in access you can easily convert it to an excel file by using the export funcions. And if the data is comma separated, there is no reason excel cannot open it. Thought I'd throw myself into the mix - I deal with data conversion daily (and crapccess is not a very friendly program).

-Cory

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

cjmiller2213 wrote:
etos45 wrote:
Chiclo wrote:
etos45 wrote:
greg wrote:Close... the comma-delimited style is used by Access.
Argh!! Now I have to learn another program? :D
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! :P Not to mention it entered my Qty in for me.
Sorta, not really. It's still all part of Office.
Yeah... but... it's, like, the SOUTH side of Office... I only chill here on the North to North-East side of Office with Excel and Word. 8-)
I am confused as to why you can't use this list in excel. I don't really know what you are talking about - but I do know if you have data in access you can easily convert it to an excel file by using the export funcions. And if the data is comma separated, there is no reason excel cannot open it. Thought I'd throw myself into the mix - I deal with data conversion daily (and crapccess is not a very friendly program).

-Cory
This list can be used in Excel.

I just mentioned Access because that's where I use it.
(...and it only sucks for the first four or five years. After that it rocks the house.) :wink:

If someone does a "cut-and-paste" of the data, into a regular text file, like Notepad...
Then change the extension from ".txt" to ".csv", it should open in Excel.

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

...which reminds me... I solved all these problems last year. :lol:

Just choose a collection from the list:
http://www.valiantcomics.com/valiant/ma ... select.asp

Scroll to the bottom of the page...
http://www.valiantcomics.com/valiant/ma ... asp?id=999
...and look for a link that says:
Create a Microsoft Excel file for this collection.

Problem solved. :P

User avatar
etos45
I live to be inefficient!
I live to be inefficient!
Posts: 4141
Joined: Mon Sep 27, 2004 8:41:32 pm
Location: Anywhere but here... nope, there I am.

Post by etos45 »

greg wrote:...which reminds me... I solved all these problems last year. :lol:

Just choose a collection from the list:
http://www.valiantcomics.com/valiant/ma ... select.asp

Scroll to the bottom of the page...
http://www.valiantcomics.com/valiant/ma ... asp?id=999
...and look for a link that says:
Create a Microsoft Excel file for this collection.

Problem solved. :P
Yeah... I noticed that when I was looking at my lovely pie charts. :)

I have one last question for you if you can answer it... I've got everything I want on my little excel list... I finally figured out the whole total issues, total individual, total needed deal, but I want it to do the point calculation too (because, as I said, it's so dearn fun :D ). But I can't figure out the formula for crap. I know it's something like 1 point per dollar for the first issue and then it's cut in half every time after that, but how do you formulate that? I figured it probably uses the IF function with some kind of loop, but that's about as far as I could get it.

User avatar
greg
The admin around here must be getting old and soft.
The admin around here must be getting old and soft.
Posts: 22248
Joined: Wed Feb 04, 2004 9:39:27 am
Valiant fan since: Rai #0
Favorite character: Depends on title
Favorite title: Depends on writer
Favorite writer: Depends on artist
Favorite artist: Depends on character
Location: Indoors
Contact:

Post by greg »

etos45 wrote:
greg wrote:...which reminds me... I solved all these problems last year. :lol:

Just choose a collection from the list:
http://www.valiantcomics.com/valiant/ma ... select.asp

Scroll to the bottom of the page...
http://www.valiantcomics.com/valiant/ma ... asp?id=999
...and look for a link that says:
Create a Microsoft Excel file for this collection.

Problem solved. :P
Yeah... I noticed that when I was looking at my lovely pie charts. :)

I have one last question for you if you can answer it... I've got everything I want on my little excel list... I finally figured out the whole total issues, total individual, total needed deal, but I want it to do the point calculation too (because, as I said, it's so dearn fun :D ). But I can't figure out the formula for crap. I know it's something like 1 point per dollar for the first issue and then it's cut in half every time after that, but how do you formulate that? I figured it probably uses the IF function with some kind of loop, but that's about as far as I could get it.
I'm not sure how you'd do it in Excel.

The point system is:
"value divided by copynumber"

So, the first copy of a $5 book is worth 5/1 points (5 points).
The second copy of that same $5 book is worth 5/2 points (2.5 points).
The third copy of that same $5 book is worth 5/3 points (1.67points).
etc.

I wrote a function in Access to do the calculation, which uses a loop.

I'm sure you can do the same thing in Excel, but I don't know where. :hm:

-----
Edit:

Ok, here you go, cut-and-paste the code between the lines
into Excel's Visual Basic Editor (ALT+F11):

---------------------




Public Function GetPoints(x As Variant, y As Variant) As Double

Dim i As Integer

GetPoints = 0
i = 1

Do While (i <= x)
GetPoints = GetPoints + (CDbl(y) / i)
i = i + 1

Loop

End Function


---------------------


Then you call the function as:

=GetPoints(A1,B1)

Where
A1 = the number of copies you have
and
B1 = the value of one copy

Use any columns you like, that's just an example.
The function is GetPoints(Copies,Value)

User avatar
etos45
I live to be inefficient!
I live to be inefficient!
Posts: 4141
Joined: Mon Sep 27, 2004 8:41:32 pm
Location: Anywhere but here... nope, there I am.

Post by etos45 »

I don't know if I'm dumb or what, but this isn't seeming to work for me (oh... and really cool with the coding editor in excel). So, I pulled up the Alt-F11 Visual Basic editor. I clicked on Sheet 1. Then I pasted the code. Everything looked okay on that end, but when I went into excel and tried to call the function it gave me a name error. It's acting like the function isn't there, which makes me wonder if I forgot to do something in the VB editor to make it available?


Post Reply