Excel based tutorial for web scraping

**akphidelt** · 06-11-14, 11:41 AM

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:

$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:

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

**oilcountry99** · 06-12-14, 02:08 PM

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.

**akphidelt** · 06-12-14, 02:41 PM

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.

**oilcountry99** · 06-12-14, 05:15 PM

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

**akphidelt** · 06-12-14, 07:29 PM

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.

**akphidelt** · 06-12-14, 07:31 PM

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

**b_rad_1983** · 06-12-14, 11:21 PM

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

**b_rad_1983** · 06-15-14, 12:32 AM

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?

**akphidelt** · 06-15-14, 01:46 AM

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.

**b_rad_1983** · 06-15-14, 12:00 PM

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

**akphidelt** · 06-15-14, 04:15 PM

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?

**b_rad_1983** · 06-15-14, 08:16 PM

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

**oilcountry99** · 06-15-14, 09:23 PM

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

**akphidelt** · 06-15-14, 11:29 PM

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.

**b_rad_1983** · 07-02-14, 02:29 AM

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.

**b_rad_1983** · 07-02-14, 10:28 AM

=IF(AND(o7>0.5,n7>0.5),"match","dontmatc h")

if there is a value in the era + whip, match else dontmatch

where did i go wrong?

N	O	P
0	1.26	match <-dontmatch
4.15	0	match <-dontmatch



1.4	1.12	match
0	0	match<-dontmatch

**oilcountry99** · 07-12-14, 04:37 PM

Hey all, I've been in here before asking about text manipulation. For example I want to change all names in my spreadsheet to first initial last name such as this:

COL A COL B
Jorge De La Rosa - J. De La Rosa
Jake Peavy - J. Peavy
C.J. Wilson - C. Wilson

I totally suck at text manipulation and I'd like a formula to perform this basic task, first initial followed by a period and space then last name

TIA

**b_rad_1983** · 07-13-14, 07:40 AM

I thought we already got this working for you?

**oilcountry99** · 07-13-14, 08:57 AM

We did but the name had a (r) (l) after it
Jorge De La Rosa (R) and I wanted J. De La Rosa

Now I just have the name with no L or R designation but I can't seem to manipulate the formula for this example.

I'm still researching on line and trying to figure it but I just haven't grasped it.

Driving me nuts because it seams like a simple task.

**oilcountry99** · 07-14-14, 11:10 AM

got it!

**b_rad_1983** · 07-14-14, 05:13 PM

Great, what did you end up using?

**oilcountry99** · 07-15-14, 01:46 PM

COL A Jorge De La Rosa

COL B =LEFT(A1,1) & ". "&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1)

Result: J. De La Rosa

**oilcountry99** · 07-15-14, 08:16 PM

I've got a fresh challenge for any takers....

On SHEET1 I have several rows of data and in each row I want to COPY a range of cells ie) AA2 to AR2

On SHEET2 I'd like to paste that range of cells AA2 to AR2 to COL A. (Paste Special Transpose will convert row to column so that much I understand)

Now that I've got AA2 to AR2 in COL A of SHEET2, I want to grab the next row in SHEET1 AA3 to AR3 and paste it into SHEET2 below the data I just pasted. I want to repeat this process until all the rows are complete.

What would be the most effective code to accomplish this task?

Any ideas are greatly appreciated, Than You.

**b_rad_1983** · 07-17-14, 03:45 PM

I think you need to be a bit more specific

Originally posted by oilcountry99

I've got a fresh challenge for any takers....

On SHEET1 I have several rows of data and in each row I want to COPY a range of cells ie) AA2 to AR2

On SHEET2 I'd like to paste that range of cells AA2 to AR2 to COL A. (Paste Special Transpose will convert row to column so that much I understand)

Now that I've got AA2 to AR2 in COL A of SHEET2, I want to grab the next row in SHEET1 AA3 to AR3 and paste it into SHEET2 below the data I just pasted. I want to repeat this process until all the rows are complete.

What would be the most effective code to accomplish this task?

Any ideas are greatly appreciated, Than You.

**oilcountry99** · 07-17-14, 05:01 PM

Got it, used Do While...Loop

**oilcountry99** · 07-19-14, 01:08 PM

Is there a way to convert the cell results of a formula to plain text or the equivalent of typing it in? I've tried copy paste/values to no avail.

I'm trying to reference the cell and it seems to only work if the text is typed in from scratch. So no formula or paste values short cut seems to work. I've discovered this through much trial and error to be my main problem.

There must be a way to convert these cell result to a usable format for INDEX/MATCH. The thought of typing all this in is beyond discouraging.

Any help from the gurus would be a real time saver. I will continue to search and experiment to achieve my results.

**oilcountry99** · 07-24-14, 06:28 PM

Well I typed it all in.....F^&^%

Now I want to copy a value from one sheet to another.

I want to find the last row and then copy the value in that row and back 8 more rows.

So if my value gets copied to A29, i want the same value copied back to A28,A27,A26......A21

I can find the last row I just don't know the formula to count back and paste the data 8 more times

After searching the net, I know of no other place to ask that's why I come here but I'm quickly learning I need to find a new outlet.

OIL

**thom321** · 07-24-14, 09:27 PM

Could you upload an example workbook where the cell result can't be used the way you want it. From your description, it should definitely be possible.

Originally posted by oilcountry99

Is there a way to convert the cell results of a formula to plain text or the equivalent of typing it in? I've tried copy paste/values to no avail.

I'm trying to reference the cell and it seems to only work if the text is typed in from scratch. So no formula or paste values short cut seems to work. I've discovered this through much trial and error to be my main problem.

There must be a way to convert these cell result to a usable format for INDEX/MATCH. The thought of typing all this in is beyond discouraging.

Any help from the gurus would be a real time saver. I will continue to search and experiment to achieve my results.

**thom321** · 07-24-14, 09:44 PM

Are you looking to do it using VBA code or using regular formulas? I have attached a workbook that does what your description says using formulas but it is very possible that I misunderstood what you are trying to do.

Copy values.xlsx

Originally posted by oilcountry99

Well I typed it all in.....F^&^%

Now I want to copy a value from one sheet to another.

I want to find the last row and then copy the value in that row and back 8 more rows.

So if my value gets copied to A29, i want the same value copied back to A28,A27,A26......A21

I can find the last row I just don't know the formula to count back and paste the data 8 more times

After searching the net, I know of no other place to ask that's why I come here but I'm quickly learning I need to find a new outlet.

OIL

**oilcountry99** · 07-25-14, 11:25 AM

Originally posted by thom321

Are you looking to do it using VBA code or using regular formulas? I have attached a workbook that does what your description says using formulas but it is very possible that I misunderstood what you are trying to do.

[ATTACH]70947[/ATTACH]

Appreciate your efforts THOM321, I'm looking to do this with VBA. I think I need code that will find the last row in COL A, and then paste the data in the Last Row in COLB and back towards the top 8 more rows.

**oilcountry99** · 07-25-14, 11:47 AM

Originally posted by thom321

Could you upload an example workbook where the cell result can't be used the way you want it. From your description, it should definitely be possible.

I'll see what I can do, but basically the cell I'm referencing has a formula {=LEFT(B1,1) & ". "&RIGHT(B1,LEN(B1)-FIND(" ",B1)+1)} in it that converts a name from sheet1 Mike Smith, to a First Initial Last name format on sheet2 M. Smith.

Because I'm referencing M. Smith on sheet2 but the cell is created by a formula the INDEX MATCH is not recognizing it. Makes no sense to me....

**thom321** · 07-25-14, 03:18 PM

Try this and see if it works for you

Sub CopyValues()

Dim rngLastCell As Range

Dim rngCopyRangeFirst As Range
Dim rngCopyRangeLast As Range
Dim rngCopyRange As Range
Dim iCopyCount As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Find last row in column A. Assumes no more than 60,000 values in column A
Set rngLastCell = Range("A1").Offset(60000, 0).End(xlUp)

'get number of times to copy last cell
iCopyCount = 8 'this value could be from a cell rather than a fixed number

'set range to copy to
Set rngCopyRangeFirst = rngLastCell.Offset(-iCopyCount, 1)
Set rngCopyRangeLast = rngLastCell.Offset(0, 1)
Set rngCopyRange = Range(rngCopyRangeFirst, rngCopyRangeLast)

'copy values
rngCopyRange.Value = rngLastCell.Value

'reset calculation to automatic
Application.Calculation = xlCalculationAutomatic

End Sub

Originally posted by oilcountry99

Appreciate your efforts THOM321, I'm looking to do this with VBA. I think I need code that will find the last row in COL A, and then paste the data in the Last Row in COLB and back towards the top 8 more rows.

**oilcountry99** · 07-26-14, 09:09 PM

Thanks Thom321, I'll try and incorporate this into my code and let you know the results!

Your time is greatly appreciated! All the best of success to you.

**oilcountry99** · 07-28-14, 07:30 AM

Thom321, I achieved my goal and got my macro to do what I needed. I didn't use your exact sub (CopyValues) but it triggered some ideas, thanks again!

**thom321** · 07-28-14, 09:10 AM

Glad you got it working.

Originally posted by oilcountry99

Thom321, I achieved my goal and got my macro to do what I needed. I didn't use your exact sub (CopyValues) but it triggered some ideas, thanks again!