Importing multiple files from internet into excel?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Motor City
    SBR Hustler
    • 04-21-10
    • 97

    #1
    Importing multiple files from internet into excel?
    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
    SBR MVP
    • 01-09-09
    • 3374

    #2
    Sports betting and handicapping forum: discuss picks, odds, and predictions for upcoming games and results on latest bets.
    [B][B]They key isn't getting rich quick. The key is getting rich slowly, and enjoying it.

    [/B][/B][SIZE=1][URL="http://forum.sbrforum.com/sbr-points/490161-points-available-loan.html#post4633361"][/URL][/SIZE]
    Comment
    • MonkeyF0cker
      SBR Posting Legend
      • 06-12-07
      • 12144

      #3
      Uhh. That's not what he's asking.
      Comment
      • MonkeyF0cker
        SBR Posting Legend
        • 06-12-07
        • 12144

        #4
        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.
        Comment
        • thom321
          SBR High Roller
          • 06-17-11
          • 112

          #5
          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.


          Using the add-in doesn't require VBA knowledge but if you know VBA, you can get more out of it.
          Comment
          • tukkk
            SBR Sharp
            • 10-04-10
            • 391

            #6
            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
            Comment
            • a4u2fear
              SBR Hall of Famer
              • 01-29-10
              • 8147

              #7
              You can also do a web query in Excel if copy/paste is not working. Then you can manipulate the data
              Comment
              • arwar
                SBR High Roller
                • 07-09-09
                • 208

                #8
                use a scraper
                Comment
                SBR Contests
                Collapse
                Top-Rated US Sportsbooks
                Collapse
                Working...