[Bettingbrothers]

I want to set up a spreadsheet, probably in excel, that will import the matchups in MLB, each teams Information such as RPI, last 10 record, streaks, etc.

I'd like the end result to look something like:

Road team, RPI, L10, Streak, starting pitcher record
Home team, RPI, L10, Streak, starting pitcher record

I've done some research on importing tables and lookup but I am completely confused.

Does anyone have anything similar to this? I currently have temporary access to Office'10, but I predominantly use OpenOffice(a free version).

[VegasVixen]

I do it in excel. I run a marco daily that imports various data onto an excel sheet(s). Then on a different sheet I have "lookups" set up that will find the current data I need off the other sheets and run my formlas.

So, you may need one sheet for the team records, one for pitcher records, etc. These sheets will update daily with current stats. Then you have a different sheet (in the same workbook) for the day's matchups which will "find" the stats you need for each game.

[Bettingbrothers]

Thanks VV. That seems to be what I am looking for. Could you provide me a place to start with the macros and lookup code?

[rfr3sh]

in excel you dont even have to know how to code much, you can record a macro , where you just click or do whatever you want the macro to do and it converts it into code and saves it

[Bettingbrothers]

I understand how to use a macro, I'm just not sure where to start to set it up.

[rfr3sh]

press record macro... i just told you , you dont need to know any programming

[uva3021]

find the websites you want to use, import the data source through the "import data source" button, record yourself organizing the data

to lookup data from an index point use "LOOKUP" , "VLOOKUP" or "OFFSET(..(Match(..,..,..),..)"

[Bettingbrothers]

I have imported a grid with the schedule, the standings and the rpi. All are on separate sheets. My next goal is when I open the spreadsheet it automatically populated with today's matchups. Does anyone have a specific way to set up a macro or code to do this? I do not know how to use the "lookup".

[mark49]

you can import the current days matchups from any number of sites, which might be easier. then use those to lookup any additional stats / info from the other tables you have already.

Lookup is easier than you think, have a look at some examples on MrExcel website and you will soon pick it up.

[VegasVixen]

The lookup code depends on what data you want to retrieve...

As a simple example, lets say you want to use starting pitchers ERA and team OPS in your function. I'll walk you thru ERA first...You will want to import this data onto one page of your workbook, so call one "pitchers", then decide which website you will use for the data. In excel, you can do a function which will import an entire chart (or charts) from a website into excel. While in excel, find the tab or menu for "Data" then click "from web". A little box will pop up with your internet homepage. Type in the website you want to use, then click each area of the webpage you want to import and click Import. It will copy the webpage data into excel. Now do the same on a 2nd page for OPS, or any other stat you will use in your handicapping function.

Finally you will set up the main page which does your functions to handcap the game, this is where the "lookup" occurs. For example, if you have Cubs - Carlos Zambrano and in the next cell you want Zambrano's ERA. That empty cell is where you do the "VLOOKUP" function and tell it to look for Carlos Zambrano on the pitchers page and retrieve the ERA stat.

You set this up for every stat you will need in your function.

Finally, everyday you will simply open the workbook and under Data click "refresh all". It will update each page where you have data off a website and update those stats with whats currently on the website. That way you won't have to manually lookup stats everyday for every team and pitcher.

If you need more help with importing data or VLOOKUP in exel just google how to do it, or, there's a good wizard in excel to help.

Best of luck;

~VV~

[Bettingbrothers]

Thanks for the help.

[Bettingbrothers]

Vixen, Can you take a look at the errors?

[thom321]

Vixen, Can you take a look at the errors?

The names you are using to as lookups don't match the names in the tables where you want to pull in data from. Also, you should make the lookup table reference into fixed cell references e.g. $A$1:$D$10. Otherwise the range will move outside of the table you are pulling data from as you copy the formula down. I also advice setting the last parameter in the vlookup to FALSE rather than TRUE since it will force Excel to find an exact match, which is what you want in this case.

I fixed the two columns with formulas in the sheet you had and set up two separate columns with the correct lookup name for each team for each data sheet since the "Standings" and "RPI" sheets use different names for the same team e.g. one sheet uses "Kansas City" and the other uses "Kansas City Royals" to refer to the same team. You should be able to use those two columns of formulas to set up the remaining ones.

[infamousbacardi]

Nice of VV to hook it up for you...only at SBR! Well done VV.

[VegasVixen]

The "#NA" error on your matchups sheet is because you are using different labels/names fotr the teams. For example, you are "looking up" NY Yankees on the calculations page, but on that page they are called "New York Yankees". The labels have to match or the lookup won't find the value you want.

[Bettingbrothers]

I thought the lookup would find a part of the word i.e. Kansas=kansas city. Makes sense though.

What about the L10 fields coming in as dates rather than text?

[thom321]

I thought the lookup would find a part of the word i.e. Kansas=kansas city. Makes sense though.

What about the L10 fields coming in as dates rather than text?

I am not VegasVixen but I hope it is ok for me to offer my input. Yesterday when I fixed the errors in the workbook you asked about, I was not very detailed in my answer so I am glad to see that VV added a more detailed explanation as to why the errors were occurring.

As far as the data for L10, the reason that is happening is that Excel by default will format anything that looks like a date as an actual date. Dates in Excel are represented by numbers (starting from 1 which is Jan 1, 1900) where for example the number 40668 is May 5, 2011. Since something like 5-5 could be a date format used to display May 5th in Excel, by default Excel will convert anything that is written as 5-5 to May 5th of the current year. To prevent this from happening, when you set up the web query, you will have to go into Options in the "Edit Web Query" window (the last window that shows before you click "Import" to run the query) and check the box that says "Disable date recognition". You can also change this for an existing web query by going to Data\Connections. Select the connection you want to change then click the Properties button on the right. Then click the tab that says "Definition" and then click "Edit Query" in the bottom left corner. Now you will see "Options" in the top right corner. If you click it you will have the option to check the box for disabling date recognition.


I already did this change for the web query used in the "Standings" sheet but I don't know if there are other web queries where you need to change this as well.

[uva3021]

Don't use data import method, its a resource hog and it messes up your lookups because its shifting cells, even absolute references can be disrupted. DOM is better, treat the HTML as an XML tree, find the corresponding nodes pointing to the table data and grab each cell value.

I've attached a sheet, not sure what you want in the Pitcher % box. If you want listed pitches just extract the lines from pinnacle. The sheet is already updated, to do get new data, click the START button.

[thom321]

uva's solution is definitely preferable and similar to what I would have done if I had done it myself from scratch. However, I think web queries have a place when you don't know programming and are not processing a lot of data and speed is not an issue. I also use them occasionally myself if I just need a quick way to get certain tables of data to Excel but I would only use them to get the data and remove the query itself (which does require programming).

[Bettingbrothers]

Thank you all for the help. I finally got a new com

[Bettingbrothers]

Thank you all for the help. I finally got a new comouter and can spend more time playing with it than on my lunch.