[Motor City]

I know their are a million threads based on simply importing data from the web into excel, but I didn't see anything on if there's anyway to actually import excel files from a website into my excel workbook.

For example, if I wanted to import all this data from this webpage (http://www.fangraphs.com/projections...bat&type=rzips) into my excel file, I could just click "Export Data" and from there I could just copy and paste all the data onto one of my excel sheets.

I have multiple files, of that same type, I would like to download daily in order to run my model. It's such a tedious task to download several of those files everyday and I was just wondering if there was anyway I could possibly automatically download all those files all at once into my excel workbook.

Thanks and I would appreciate it so much for any help because this is driving me crazy.

[big0mar]

http://www.sportsbookreview.com/forum/handicappe...ml#post4024365

[MonkeyF0cker]

http://www.sportsbookreview.com/forum/handicappe...ml#post4024365

Uhh. That's not what he's asking.

[MonkeyF0cker]

1. You don't need to copy and paste. The data comes in a CSV file which can be imported into any Excel spreadsheet in a few steps. (Data Tab, From Text)
2. Any type of automation into Excel is pretty clunky. Excel worksheets are COM objects and can only be accessed through COM interop. Whatever language you'd use to code any sort of automated scraper for the data would need to use a COM interop assembly to write directly to your spreadsheet. It's a very ugly process for the most part.
3. Since you're downloading CSV files, you can automate the process rather easily. There are command line tools or macro tools (like iMacros for FireFox) that allow you to schedule downloads of binary files.
4. If you need further automation in Excel (like further customizing the importation of the CSV files beyond what the wizard allows), you should learn VBA.

[thom321]

If you decide to go the VBA route, if all the data you need comes from the Fangraphs site, rather than automating the reading of the data from the text file (csv) that is generated when you click "Export data", you can use the team specific urls which would show a small enough set of data that it is all displayed on one page. Then you can loop through the urls for each team and import the data that way.

The process would be something like this:
Create the team specific url
Import the html code as a string using e.g. XMLHTTP
Parse through the string, extract the relevant data and store in array

Repeat for all teams
Write the data from the array to the spreadsheet.

If you plan on doing a lot of work in Excel, I reiterate what Monkey said and highly recommend learning VBA. I also recommend downloading the free and open source "Stock Market Functions" add-in and take a look at the code used for getting data from the web to Excel.
http://finance.groups.yahoo.com/group/smf_addin/

Using the add-in doesn't require VBA knowledge but if you know VBA, you can get more out of it.

[tukkk]

thanks for the link, ive been doing a lot of work on that matter, im sure ill get a few tips out of the code

[a4u2fear]

You can also do a web query in Excel if copy/paste is not working. Then you can manipulate the data

[arwar]

use a scraper