0

I would like to import some data from an online HTML table and place it in a spreadsheet that follows another formatting system. Let me explain. I am keeping track of a baseball team's results for a season, and more specifically hits, home runs and RBIs. The data is posted here and is organized according to the dates of the events. In my spreadsheet, I also organize the data according to the dates of the events, but with the exception that my column with dates (first column) contains dates for the entire season and not for specific events. Days off (when there are no events) are automatically marked as null by Excel. Additionnaly, the dates in my spreadsheet are formatted like this (DD/MM) whereas the dates online are formatted like this (Mnth DD). Also, I am interested in keeping track of only certain results (therefore certain columns of the HTML table) Is there a way for me to import specific portions (rows) of data, preserve my formatting (DD/MM + days off marked as null) and have the data place itself on the corresponding row? Ideally the data would update itself as the season progresses. Please have a look at the image in case all of this is unclear.enter image description here

Community
  • 1
  • 1
gmorissette
  • 291
  • 1
  • 3
  • 13
  • 2
    All of that is do-able, but what have you tried? Specifically - *what is preventing you from achieving this goal* ? – Tim Williams Jul 25 '14 at 22:15
  • My programming skills! I know nothing about VBA or Javascript or Python – gmorissette Jul 25 '14 at 22:16
  • 2
    So basically you're asking for someone to write the code for you? Did you try (for example) using the built-in tool *Data >> From web* and building on that? – Tim Williams Jul 25 '14 at 22:18
  • Not necessarily. Someone could write part of the code or suggest me to have a look at a specific example. Not lazy. Just clueless. – gmorissette Jul 25 '14 at 22:21
  • You can achieve most of this using the Share option on that webpage and then taking the results as comma-separated values which you could paste into Excel – barrowc Jul 26 '14 at 02:33
  • Yes thank you! But then how do you automate the process? I've tried Excel's 'data report' option but it won't allow me to extract specific parts of the HTML table – gmorissette Jul 26 '14 at 02:36
  • I only get one big chunk of data that loses all formatting I apply to it once I refresh the data – gmorissette Jul 26 '14 at 02:37

1 Answers1

1

Make a new worksheet. Go to Data - From Web, navigate to that site, select whole page (I couldn't see how to just get the table you want) and Import it.

This new worksheet will be your data page and the other page (your existing sheet) will be your presentation page. On the data page, go to AG86 (or thereabouts) and put this formula in

=IF(ISERR(FIND("(",C86)),C86,DATEVALUE(LEFT(C86,FIND("(",C86)-1)))

Fill that down to AG1000. That will clean up problems with dates that come from that website.

Now if you want to get home runs, go to your presentation page and, assuming you have dates in A1:A?, but this formula in B1

=IF(ISNA(MATCH(A2,Sheet1!$AG$86:$AG$1000,FALSE)),"",INDEX(Sheet1!$M$86:$M$1000,MATCH(A2,Sheet1!$AG$86:$AG$1000,FALSE),1))

Finally, every time you use the worksheet, go to the data sheet, right click on the web query and refresh it to get the latest data. You can event hide the data page if you don't want anyone else to see it.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • Amazing! I'm blown away! This is exactly what I was hoping to achieve. Many thanks. Cheers! – gmorissette Jul 27 '14 at 00:38
  • I just realized that this formula works fine for numerical data but that I get my home games marked as O (zero) when I try to copy @ symbols from my source sheet to the other. I also get green corners (formula refers to empty cells..) – gmorissette Jul 31 '14 at 20:20
  • What do you mean you get home games marked as zero? You shouldn't be copying anything from the source sheet. Just use formulas to bring over what you need. If the web query makes certain columns difficult (like the date) then make a spare column off to the right to clean it up. – Dick Kusleika Jul 31 '14 at 22:21
  • Yes I thought about that afterwards, sorry for the inconvenience – gmorissette Aug 01 '14 at 01:13