collecting data automatically from webpage

[/\ox]

"

[mark49]

I couldnt manage it with vegasinsider but i could with

covers.com/pageLoader/pageLoader.aspx?page=/data/nba/teams/pastresults/2011-2012/team404169.html

using a bit of code to download it that refreshes each time you open the workbook.

Once you have the data in excel it is very easy to change it to how you want it.

Does that site have what you need?

[/\ox]

yep, Covers is nice too. Actually I think it's more of a case of data arranging in Excel, because it's easier to just copy table from website and paste it directly into Excel but I'm novice in this field gotta learn then!

ty mark49!

[mark49]

the easiest way would be to record a macro while you arrange the table to how you like it (delete unneeded columns etc) and then run that macro whenever you update your table.

If you need to extract parts of the data (for instance the game total) then a couple of simple formulas will do it, perhaps on the next sheet so that the table appears in the way you want it.

If you are new to Excel then learing LEN, LEFT, RIGHT, FIND, TRIM etc will help.

For example if the cell A1 had the game total as O 188.5

the formula =RIGHT(A1,LEN(A1)-2) in cell A2 will return 188.5

and it would now be a number that can be used in calculations rather than text.


Hope that helps, good luck.

[/\ox]

I cannot get this formula work, can anyone tell me what's wrong with it?

=COUNTIFS(A2:A7, ">" & B6,A2:A7,"<" & B7)

Can cell B4's result come from formula?

[thom321]

The formula the way you have entered it "works", meaning that it returns the number of time the data in cells A2:A7 is greater than the value in B6 AND smaller than the value in B7.

So if you have the following values in A2:A7
2
3
4
5
3
7

and cell B6 has the value 2 and B7 has the value 4, the function as you entered it will return 2. The only value that meets the criteria is 3, and the number 3 occurs 2 times in the list of values in cells A2:A7.

Where did B4 come into this? If you mean B6 or B7, either of them can be a formula rather than a number that has been manually entered.

[/\ox]

ty thom321, I meant B6 and B7, ofcourse. by "work" I mean that it gives me error every time and suggest that formula isn't right.

[thom321]

If I take your formula, as you entered it in your previous post, and copy it to the formula bar in Excel, it works fine. The only thing that comes to mind is if the regional settings on your computer uses comma as a decimal separator i.e. 12 dollars and 25 cents would be written as $12,25 rather than $12.25. That could happen if your computers regional setting is not set to United States. The link below explains it in more detail.

http://blogmines.com/blog/2010/03/11/how-to-change-the-decimal-separator-in-excel-2010/

If that is the case, Excel could be set to use ";" rather than "," to separate arguments in a function. That would cause the error you show above.

[/\ox]

Ok, will try out those regional settings, thank you very much thom321!

[Sportsguy_USA]

Simple. Build a scraper (or hire a guy to do it) that pings the site every X frequency (daily, hourly, weekly, etc). This will grab and pull the data to your database, which you can then have some front end interface built too to query the data as you like.

Caution - hitting the site with a high frequency they may block your IP. If thisis the requirement, get a IP randomizer.

Just saying...not that I've had this done before ;-)

[Rufus]

Learn to program. Write a scraper. Any reasonably intelligent person should be able to learn a language like Perl (I had no programming experience and taught myself).

[evo34]

Simple. Build a scraper (or hire a guy to do it) that pings the site every X frequency (daily, hourly, weekly, etc). This will grab and pull the data to your database, which you can then have some front end interface built too to query the data as you like.

Caution - hitting the site with a high frequency they may block your IP. If thisis the requirement, get a IP randomizer.

Just saying...not that I've had this done before ;-)

I agree. You can get pretty high quality work done for cheap at sites like freelancer.com. I had a fairly complex financial site scraping job completed within a week with no problems. Well worth the money in my opinion.