Anyone Familiar with Excel & VBA

[cardsnstuff]

I want to make a spreadsheet with excel with cards I have, need, etc; however, I want the spreadsheet to be able to read the title column and scrape the latest ebay price or the avg ebay price {preferably from sold listings} etc. I understand there may be flaws from different titles, best offers, etc; I don't need it to be 100% accurate but the higher the better obviously, as at least it gives me a starting point.

This way I can have an accurate value sheet for when I go to shows and trade or buy, or sell for that matter. And can update it whenever I want. I saw a you tube video, the guy did golf clubs using VBA; Which I have no idea how to use. Can anyone help? I would need step by step instructions; or PM me and I'll gladly pay you for your time in helping me make this idea a reality.

Not sure if this right board; if not feel free to move

[Sean1125]

vintagecardprices.com

[cardsnstuff]

Originally Posted by Sean1125

vintagecardprices.com

I am a gold member; I was hoping to do it for stuff from 99 on.

[Tennis13]

Originally Posted by cardsnstuff

I want to make a spreadsheet with excel with cards I have, need, etc; however, I want the spreadsheet to be able to read the title column and scrape the latest ebay price or the avg ebay price {preferably from sold listings} etc. I understand there may be flaws from different titles, best offers, etc; I don't need it to be 100% accurate but the higher the better obviously, as at least it gives me a starting point.

This way I can have an accurate value sheet for when I go to shows and trade or buy, or sell for that matter. And can update it whenever I want. I saw a you tube video, the guy did golf clubs using VBA; Which I have no idea how to use. Can anyone help? I would need step by step instructions; or PM me and I'll gladly pay you for your time in helping me make this idea a reality.

Not sure if this right board; if not feel free to move

I am good enough to be dangerous but bad enough to need help. Do you have the golf club video? I would have thought he would need python for a web scraper, not vba. But if it can be done in vba, i am intrigued to possibly jump down the rabbit hole

[cardsnstuff]

Originally Posted by Tennis13

I am good enough to be dangerous but bad enough to need help. Do you have the golf club video? I would have thought he would need python for a web scraper, not vba. But if it can be done in vba, i am intrigued to possibly jump down the rabbit hole

Here's the video

https://www.youtube.com/watch?v=Q125hnZaRe0

[BoyWonder089]

I use VBA almost daily at my job. I have tried to do something similar, but found it too challenging to figure out by myself. I'll take a look at the video and see what I can come up with.

Tennis13, maybe we can collaborate to some extent rather than duplicate work?

[The-Cardfather]

Originally Posted by Tennis13

I am good enough to be dangerous but bad enough to need help. Do you have the golf club video?

You mean that this wasn't the golf club video that Scott was referring to?


https://www.youtube.com/watch?v=HKFKSmU3Kb4

[Tennis13]

Originally Posted by cardsnstuff

Here's the video

https://www.youtube.com/watch?v=Q125hnZaRe0

So, this is pretty easy. I didnt realize it was this easy. However, it's bad code because he hard codes in addresses. Its not smart enough to go crawling on its own. So its right inline with my experience of good enough to be dangerous to write bad code, but it can get you to where you want to be if you dont mind dropping in the link manuually. This is easy.

[cardsnstuff]

Originally Posted by Tennis13

So, this is pretty easy. I didnt realize it was this easy. However, it's bad code because he hard codes in addresses. Its not smart enough to go crawling on its own. So its right inline with my experience of good enough to be dangerous to write bad code, but it can get you to where you want to be if you dont mind dropping in the link manuually. This is easy.

Cool; and THANK YOU; but what does that mean; dropping link manually; Is there anyway to avoid that and do it automatically?; I envision a spreadsheet with thousands of items, eventually. But, hey any help is more than I can do. Thanks.

[cardsnstuff]

here's some sample items

2009-10 First Edition #192 Jrue Holiday PSA 10 GEM MINT RC Rookie MEM 76ers
2009-10 First Edition #186 Taj Gibson PSA 10 GEM MINT RC Rookie MEM Bulls
2009-10 48 #81 Dominique Wilkins PSA 10 GEM MINT Hawks
2009-10 48 #98 Clyde Drexler PSA 10 GEM MINT Blazers
2009-10 First Edition #181 Darren Collison PSA 10 GEM MINT RC Rookie MEM Hornets
2006-07 Topps #240B J.J. Redick PSA 10 GEM MINT RC Rookie Magic
2007-08 Finest #79 Nick Young PSA 10 GEM MINT RC Rookie Wizards
2013 Allen and Ginter Across the Years #CRJ Cal Ripken Jr. PSA 10 GEM MINT Orioles
2012 Chrome #45 Clayton Kershaw PSA 10 GEM MINT Dodgers
2013 Topps 1972 Topps Minis #TM-42 Willie Mays PSA 10 GEM MINT Giants
2008-09 Sunkist #3 Carey Price PSA 10 GEM MINT Canadiens
2014 Prizm Rookies #287 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Chrome Rookie Die Cuts #CRDC-JM Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Press Pass Gold #30 Johnny Manziel PSA 10 GEM MINT
2014 Finest #125 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Finest Refractor #125 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Prizm Believe the Hype #1 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns

[frankbmd]

Originally Posted by cardsnstuff

here's some sample items

2009-10 First Edition #192 Jrue Holiday PSA 10 GEM MINT RC Rookie MEM 76ers
2009-10 First Edition #186 Taj Gibson PSA 10 GEM MINT RC Rookie MEM Bulls
2009-10 48 #81 Dominique Wilkins PSA 10 GEM MINT Hawks
2009-10 48 #98 Clyde Drexler PSA 10 GEM MINT Blazers
2009-10 First Edition #181 Darren Collison PSA 10 GEM MINT RC Rookie MEM Hornets
2006-07 Topps #240B J.J. Redick PSA 10 GEM MINT RC Rookie Magic
2007-08 Finest #79 Nick Young PSA 10 GEM MINT RC Rookie Wizards
2013 Allen and Ginter Across the Years #CRJ Cal Ripken Jr. PSA 10 GEM MINT Orioles
2012 Chrome #45 Clayton Kershaw PSA 10 GEM MINT Dodgers
2013 Topps 1972 Topps Minis #TM-42 Willie Mays PSA 10 GEM MINT Giants
2008-09 Sunkist #3 Carey Price PSA 10 GEM MINT Canadiens
2014 Prizm Rookies #287 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Chrome Rookie Die Cuts #CRDC-JM Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Press Pass Gold #30 Johnny Manziel PSA 10 GEM MINT
2014 Finest #125 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Finest Refractor #125 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns
2014 Prizm Believe the Hype #1 Johnny Manziel PSA 10 GEM MINT RC Rookie Browns

If this is a prewar mainboard topic, where can I get a helmet and a foxhole. But then again I may just hide behind the water cooler.

[cardsnstuff]

Originally Posted by frankbmd

If this is a prewar mainboard topic, where can I get a helmet and a foxhole. But then again I may just hide behind the water cooler.

They were just recent samples I had, such a thing would be great for all ungraded cards too. For example: I could have provided a sample list of ungraded t206's with standard/various backs in VG

[frankbmd]

Originally Posted by cardsnstuff

I am a gold member; I was hoping to do it for stuff from 99 on.

Wasn’t this your post?

I assumed you meant 1999 and not 1899, which VCP has covered.

[cardsnstuff]

It was my post; yes; Initially I was thinking simple post war graded cards. But as I have been thinking about it more; Imagine the possibilities. You come up with virtually any list and scrape or least try to scrape recent market prices. Including ungraded ones that VCP, SMR, etc don't cover. And we all know Beckett price guide is a joke.

Personally, I am excited about the people that are willing to help and may try to figure it out.

I do understand your concerns; and I did say in opening thread if I posted in wrong section to please move it.

[Tennis13]

Originally Posted by cardsnstuff

Cool; and THANK YOU; but what does that mean; dropping link manually; Is there anyway to avoid that and do it automatically?; I envision a spreadsheet with thousands of items, eventually. But, hey any help is more than I can do. Thanks.

Dropping the link manually: If you see on his video, he is hard-coding that ebay link and pulling data from that. Ebay has very obvious links, so you can write a loop that replaces it programatically and maybe goes through every link -- like he changed some letter in the link, you could just say "find, replace" in the code and do that. In addition, it's my hunch that while ebay caps you at 200, you could probably go into that link and change the 200 to 3000. MAYBE.

You will want to migrate from a spreadsheet to a database eventually -- again, out of my area of expertise, but NOT THAT HARD and I'd bet the other guy that works in VBA probably has some views on this.

I think what you'd ideally like is to build a python webscraper, write it to a database rather than excel, and then build basic SQL queries to pull down attributes. The SQL is easy, the writing to a database is pretty easy, too. The python web scraper, i have failed building a few times, but I'm sure we could collectively stumble through it to create it.

[cardsnstuff]

I want to give a big THANK YOU to Richard, for his work, we have a working {although, it has issues} version. Once completed I will happy to share with everyone; it will be a tremendous time saver and a tool that anyone who has cards that are not in VCP database {such as raw or modern} or items {such as balls, bats, jersey's, etc} for that matter will appreciate. Can anyone continue on this working model and help us fine tune it? If you can help, please PM me.