Excel based tutorial for web scraping

Originally Posted by b_rad_1983

In the time it took you to do that, how long would it take to accomplish it in php?

Oh yea, if you know regular expressions you can do anything. For example, this is how easy it is to get the html of a website...

Code: [View]

$url = 'http://scores.espn.go.com/mlb/scoreboard?date='.$espnDate;

//get the contents of the page
$handle = file_get_contents($url);
$str     = htmlentities($handle);

Then with regular expressions you can just pick it apart. For instance this is how easy it is to get every espn game id for that day

Code: [View]

$pattern = '/(\d*)-gamebox/';
preg_match_all($pattern,$str,$gameIDs);

Now in my $gameIDs variable, I have an array with every gameID for that day, so I can then loop through those and go to the boxscores to extract the player data and in the database it would all be tied to that game id.

Just something to think about if you ever wanted to expand.

Excuse my ignorance here and complete lack of knowledge of PHP and what it is and how it works but I have a question. What would one use to create the code and where is the output displayed, what is the format? In excel for example it uses vba, the code is written in the program and output on the sheets.

Originally Posted by oilcountry99

Excuse my ignorance here and complete lack of knowledge of PHP and what it is and how it works but I have a question. What would one use to create the code and where is the output displayed, what is the format? In excel for example it uses vba, the code is written in the program and output on the sheets.

PHP is what sites like Amazon or Facebook is written in. The most common web language in the world. But, in order to process the code you have to install Apache, PHP, and most likely a database like MySql. Or purchase a hosting service that has all that available. I use Xampp for development purposes, apachefriends.org. Definitely has a long learning curve to it though. I've been doing it for 5 years now so it's a bit easier, but it was brutal at first. Especially if you don't come from a computer science background like me.

Originally Posted by akphidelt

PHP is what sites like Amazon or Facebook is written in. The most common web language in the world. But, in order to process the code you have to install Apache, PHP, and most likely a database like MySql. Or purchase a hosting service that has all that available. I use Xampp for development purposes, apachefriends.org. Definitely has a long learning curve to it though. I've been doing it for 5 years now so it's a bit easier, but it was brutal at first. Especially if you don't come from a computer science background like me.

I have zero computer science background and just got into vba recently....it's extremely frustrating and very rewarding at the same time.

Thanks for your insight

Originally Posted by oilcountry99

I have zero computer science background and just got into vba recently....it's extremely frustrating and very rewarding at the same time.

Thanks for your insight

No problem. And yes, it's very frustrating and rewarding. Really fun learning new stuff.

b_rad...

Try this out. Had some time after work. This took about 20-30 minutes. Was able to log every pick. It will take a little bit longer to make this dynamic where it goes in and updates the pending results every time. The only thing I can do right now is clear the database and rerun the code to pick up the new results. I'll work on getting it where you can click a button and update the pending results. This logged over 5000 bets.

http://akptracker.com/index.php?c=insiders

Looks good. You should start a php thread, so it doesn't get confusing in here.

How would I search a name such as D. Duffy (L) on sheet a. Then to look for that name in a different format such as Danny Duffy on another sheet, once matched, get data from a cell 3,4,5 or 6 rows over?

I'd add a column in sheet a with a formula extracting D. Duffy, which you already know how to do. Then I'd at a column with a formula in the other sheet breaking Danny Duffy in to D. Duffy and use vlookup or Index/Match depending on where you add the columns. The only problem with this is if there are two or players with the same first initial/last name.

I was thinking on not adding any columns, rather to text split from right to the first space in a formula, combined with vlookup. Object is to take the probable pitcher for the game, match it with the pitcher data that you showed me how to do, then grab the whip, and this formula will be in the whip column

Originally Posted by b_rad_1983

I was thinking on not adding any columns, rather to text split from right to the first space in a formula, combined with vlookup. Object is to take the probable pitcher for the game, match it with the pitcher data that you showed me how to do, then grab the whip, and this formula will be in the whip column

That's going to be pretty difficult. Have you figured it out yet?

I have not had time to try, i did something similar on my nhl project using search function. But I have not combined anything with vlookup yet

I would add a column like Akphidelt suggest above, that's how I typically do it. There's always several ways to achieve results that's the beauty of vba

Originally Posted by b_rad_1983

I have not had time to try, i did something similar on my nhl project using search function. But I have not combined anything with vlookup yet

I don't think vlookup would work in this case. Not sure what would without adding new columns. You can always add a column in the VBA that creates the first initial/last name identifier then you can use vlookup in the probables page. But once again that would cause problems if there are more than 1 match. That's were databases are nice because you can attack a unique identifier (uid) to each player and it doesn't matter what their names are.

Sorry guys.. I had no time to work on this. Work has picked up, so all my free time is gone!
I ended up just doing the easy text splitting to match and adding a column.