Questions relating to using excel in modelling

**brettd** · 03-02-11, 10:45 AM

One other thing, I thought INDEX and MATCH would work here, but as I understand it, this would only find the first instance of Team A before stopping.

Or am I wrong here?

**brettd** · 03-02-11, 11:10 AM

My overall objective is to recreate Justin7's first few modelling rules on pages 104-105 of his "Conquering Risk" book with my own dataset using excel. I'm not asking for any of the other stuff he did later in the chapter.

If anyone can come up with a small demo of how someone can implement these rules using excel and only a small demo dataset; I would be greatly appreciative (and I think heaps of others would too). I could then deconstruct how it was made and draw inspiration from there.

**lovesbaseball1** · 03-02-11, 03:40 PM

I'd create a column that produces a 1 or a 0 if home team is in column a, something like :
=if(iserr(find(c1, a1, 1)), 0, 1)

then sort the list, using the 1 or the 0 in the new column.

you could record a macro to do the sorting automatically.

**mrtomk** · 03-02-11, 06:05 PM

Hi,

First ever SBR post so hope its of some help. I haven't read the book you referenced, but if I understand you correctly you want to show a list of occurrences that Team A have played at home/away, and the points they scored on each occasion?

If so, see attached link. Cell T3 has a drop down list of the various teams (Teams A, B & C), coming from column A. Select the team from the list and it should show you the points they scored in each of their Home games, and Away games. Note that this doesn't pick up the opposing team's scores as I assumed from your post this wasn't needed, instead Game No.1 shows Team A's first Home game score in column V, and first Away game score in column X.

I created columns C & D, which pick out each time the selected team plays at Home (C) and Away (D). The vlookup in the table runs from these and picks up the first instance of the number. Hide these if they get in the way.

Hope that makes some sense. Let me know if there are any questions. There is most likely a easier macro way of doing this, but Im not great with them...

Attached Files

HomeAwayScores.xls (25.5 KB, 270 views)

**mrtomk** · 03-02-11, 06:39 PM

PS Adjust the vlookup to include the whole column if this is a list you are permanently adding to. i.e. in my example, in column V, change $C$2:$R$22 to just $C:$R, in column X change from $D$2:$R$22, to $D:$R.

Also, copy the formulae in columns C & D down for your whole list of scores.

Regards

**brettd** · 03-02-11, 10:54 PM

Mate that was pure gold.

I've played around with the sheet and it's extracting all the scores. One final thing, how do I edit the formula to sort into chronological order? Let's say column G and H had "Year" and "Round" information that would enable us to chronologically sort the info.

So the concept of the output would look something like this:

Game No, Home / Away?, Year, Round, Score

1 , Home , 2006, 1 , 40

Cheers

**brettd** · 03-02-11, 11:28 PM

Not to worry, have just figured it out. I added in new columns, copied and pasted the formula, and changed the column index number within the formula. Worked a treat.

Thanks heaps!

**subs** · 03-03-11, 01:18 AM

mrtomk - welcome - can u post every day?

**jane2geo** · 03-03-11, 09:21 AM

Wow, I was going to suggest something simple like this. Place this formula in the cell you wish the accumulated TeamA totals to appear. =SUMIF(C2:C999,"TeamA",N2:N999 )+ SUMIF(D2

999,"TeamA",Q2:Q999)

**jennahazeplays** · 03-03-11, 09:42 AM

i think youre right but not sure

**mrtomk** · 03-03-11, 11:21 AM

Yep, my initial thought was to go with a sumif, but I think he wanted a list of every individual score in a list, as oppose to a sum of all the scores, which is why I went the long way around.

**mrtomk** · 03-03-11, 11:25 AM

No worries, brettd, glad I could help...

**Ralphie88** · 03-03-11, 07:04 PM

I have some experience using Excel, but I am nowhere near as proficient as mrtomk, and I have absolutely no experience creating or using models, but like brettd I would like to learn. So I hope my questions are not too moronic. First of all, if I am using Excel to create my database/model, what are the most important Excel functions I should know/learn? Secondly, I am kind of having a difficult time grasping the model concept. What exactly does one want to include in their model? From everything I have read in "The Think Tank" and from various books, am I correct in assuming that I want to try and use as many stats as possible that will help me determine a score or total and than based on the outcome that my model produced I want to compare that to the opening/closing lines to see how effective my model is?

Any help would be appreciated. Thank you.

**subs** · 03-03-11, 09:26 PM

i a rookie at this really, but there's no need to start from scratch...

look at kenpom and sabermetrics. try some out ---> produce own lines ---> realize that the books r better than u ---> get better ---> realize that books r still better than u ---> review options ---> get better etc etc

good luck

**brettd** · 03-04-11, 07:23 AM

Ralphie, I was in the same boat.

Go through the online spreadsheet modelling course by Wayne L Winston available at HBS online, it's a good primer for learning some very useful functions (Winston is also an avid sport handicapper as well). Get Justin's Conquering Risk book, and attempt to start recreating models from his book using excel.

That's where I'm at now.

**brettd** · 03-04-11, 09:48 AM

I'm now trying to get a "Days since last game" variable to my excel spreadsheet data for the home and away teams of each recorded match.

How can I tell excel to check for an entry of 'Team A' in the HomeTeam column; then to find the last previous entry of 'Team A' higher up in the list (going back in the past), and then comparing these two dates?

My columns are like this:

HomeTeam AwayTeam Location Date

Team A Team B Ground X 25-Mar-06

mrtomk? anyone else got an idea? Thanks in advance.

**mrtomk** · 03-04-11, 12:36 PM

Hello All,

Afraid I'm behind you guys when it comes to actually developing models for stats and lines, I've just been learning the basics from this forum. So I dont know what to recommend in terms of specific functions. Only reason I know any Excel is because Ive had to use it in every job Ive had. With that in mind, there will likely be cleverer ways to solve your problems that my solutions.

With that disclaimer, one way I can see of solving your problem, brettd, is with vlookups against columns C&D once again. In the attached sheet, column AC looks up the date for the relevant home game number, then looks up and subtracts the date of the preceding home game number. Because Excel stores dates as numbers, it will leave you with the difference between the two dates.

The if(iserr( stuff is just to prevent #N/A popping up when there wasn't a previous home game, or a game number found. The important part is =VLOOKUP($X2,$C:$L,10,0)-VLOOKUP($X1,$C:$L,10,0), and would work equally well on its own.

Hope that helps...

Attached Files

HomeAwayScores.xls (28.0 KB, 187 views)

**jane2geo** · 03-04-11, 01:03 PM

Mr Tom where were you when I was building my spreadsheets, Bravo.

**mrtomk** · 03-04-11, 03:29 PM

On review, it occurred to me you might have wanted days since last game, instead of, or as well as, last home/away game, so I've reviewed with AC, AD and AE calculating the days since the last type of game. Besides, the sheet in my last post was slightly confusing to read. I think this one makes more sense by putting in a total games played column and working off that.

If you need to get back to having just home scores, or away scores, just use the simple filter for NonBlanks on Home/Away Scores in the side table.

It actually occurred to me just now that you probably could have achieved most of what you wanted just by applying the filter for the team you wanted to your main sheet in the first place, which would've saved a lot of formula writing. Good thing I enjoy messing around in Excel.

Anyways, any other problems, let me know...

Attached Files

HomeAwayScores.xls (35.0 KB, 192 views)

**Ralphie88** · 03-04-11, 03:53 PM

Originally posted by brettd

Ralphie, I was in the same boat.

Go through the online spreadsheet modelling course by Wayne L Winston available at HBS online, it's a good primer for learning some very useful functions (Winston is also an avid sport handicapper as well). Get Justin's Conquering Risk book, and attempt to start recreating models from his book using excel.

That's where I'm at now.

Thanks brettd. Would you recommend reading the book first (Mathletics) or did you learn more from the online course?

**brettd** · 03-04-11, 07:40 PM

Ralphie, I also read Mathletics in addition to Conquering Risk before before I started the excel course. I suggest you do so in the same way.

There was a heap of spreadsheets that you can download to accompany your reading of Matheltics; you will find it easier to interpret/deconstruct these spreadsheets with the excel knowledge you will learn in the course.

Hey mrTomk, I'll take a look at your new spreadsheet solution now. Many thanks!!!

**brettd** · 03-04-11, 08:55 PM

Hey mrtomk,
I've fiddled around with it by inputting my own data into the cells. As you can see, i'm getting some zeros for the "days since last game" column. I'm not sure why this is. Any idea why this is happening?

Thanks in advance.

Attached Files

HomeAwayScores_revised.xlsx (61.1 KB, 175 views)

**mrtomk** · 03-04-11, 10:28 PM

Hey brett,
I dont have a legit version of Excel on this pc so I cant play with your spreadsheet, just look at it. But it seems the problem is in the formulae in columns B, C, D. For example, the formula in cell B4 needs to be looking across to cells E4 and F4, not down to E5 and F5. Essentially, GamesPlayed is saying does the game in this row include the team selected in T1, if so, add 1 to the Games Played total from the cell above (B3+1), if not give me the old total (B3). So B4 should read:
=if(or($T$1=E4,$T$1=F4),B3+1,B3)

Play with that and let me know if it works, if not I'll have a closer look...

**mrtomk** · 03-04-11, 10:30 PM

PS Change the cells to look at the same row for columns C & D too, as yo did in B.

**mrtomk** · 03-05-11, 04:23 PM

Should work now. See attached...

Attached Files

HomeAwayScores_revised.xlsx (59.5 KB, 194 views)

**brettd** · 03-06-11, 11:10 PM

Hey mrtomk,
Again awesome spreadsheet. Is it possible to split the 'Days Since Last Game' column into separate 'Home Days Since Last Game' and 'Away Days Since Last Game'?

I've just spent the last 90 minutes getting separate Home and Away days since last game going for each match entry. Even though i'm now 'done' doing this, I'm pretty sure I've managed to stuff something up in the heap of filters, sorting and copy and pasting I was doing. My results are probably no good anyway.

Mrtomk (or anyone else for that matter), is there any way we can extract both the Home and Away 'Days Since Last Game' figure for each match entry?

**mrtomk** · 03-07-11, 11:25 AM

Hey brettd, I'm confused. Does this differ in some way from Days Since Last Home Game and Days Since Last Away Game in columns AC & AD? What do you mean by a Home/Away day?

**brettd** · 03-07-11, 10:57 PM

Originally posted by mrtomk

Hey brettd, I'm confused. Does this differ in some way from Days Since Last Home Game and Days Since Last Away Game in columns AC & AD? What do you mean by a Home/Away day?

I think I could best explain this with an example. Let's pick East Fremantle vs East Perth on 08-Apr-06. I would like to know how many days it's been since East Fremantle played last, and the amount of days it's been since East Perth played last. Then I would like the same info for every match entry.

Can this be done using some sort of vlookup, or does this get into the realm of VBA for excel?

**mrtomk** · 03-08-11, 01:45 PM

Hello again brettd,

Like all of my entries, I begin with the disclaimer that there may well be a nicer way of doing this with VBA, but my knowledge there is very limited, so I couldnt tell you either way. Maybe some one else reading could help there?

Attached is my non-VBA solution. I can't take full credit as I had to google a way to lookup the last occurrence of a value in a range, but it works nicely. Cells in columns C & D look up the latest date in P on which the home/away team played from all those games above the current row, and subtract that value from the date of the game in question.

All the best

Attached Files

HomeAwayScores.xls (955.5 KB, 209 views)

**brettd** · 03-08-11, 10:32 PM

Cheers mrtomk,

Once again you've managed to help me out! The solution worked a treat.

**mrtomk** · 03-09-11, 01:21 PM

No worries. Happy to chip in what knowledge I have. Don't hesitate if any new problems crop up... BOL

**threeg5** · 03-09-11, 07:32 PM

Originally posted by subs

i a rookie at this really, but there's no need to start from scratch...

look at kenpom and sabermetrics. try some out ---> produce own lines ---> realize that the books r better than u ---> get better ---> realize that books r still better than u ---> review options ---> get better etc etc

good luck

Why not continue this journey and then join the darkside!!
Go work for a book as a bookies statitioneer hell after all if you can't beat'em join right?

3g5

**andrewharry** · 03-10-11, 05:40 AM

Its good to see such a response, I am having problems related to importing data from database located on server into my options excel file. How to go about it this process has to be carried out on a regular basis and I am not that good in server and client side technologies.

**jane2geo** · 03-10-11, 12:28 PM

More details would help……….!