I need to automate the calculations in my model.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Sportsfan800
    SBR Hustler
    • 12-03-09
    • 64

    #1
    I need to automate the calculations in my model.
    I'm serious about building a succesful model to predict baseball games. I have collected all the historical data that I need from baseball reference dot com. I have found a run estimator on the internet. I'm using Jim Furtado's Extrapolated Runs Formula. I'm going through each line-up card and calculating singles, doubles, triples, etc... The problem is this is very tedious and time consuming. It takes an hour each game to do it by hand. Is there a way to automate this process? Can I have the computer calculate everything for me? The calculations are simple. Addition, subtraction, multiplication and division. Should I use team stats to speed up the process? I hesitate to do this, because I will lose some accuracy. I don't have sixteen hours a day to devote to baseball handicapping. Any suggestions?
  • roasthawg
    SBR MVP
    • 11-09-07
    • 2990

    #2
    Excel is your friend for stuff like this.
    Comment
    • Sportsfan800
      SBR Hustler
      • 12-03-09
      • 64

      #3
      Originally posted by roasthawg
      Excel is your friend for stuff like this.
      I don't have Excel.
      Comment
      • sharpcat
        Restricted User
        • 12-19-09
        • 4516

        #4
        Originally posted by Sportsfan800
        I don't have Excel.


        Where are you storing your database? Most of the functions of Excel can be executed in Numbers on a mac.
        Comment
        • oldstylecubsfan
          SBR High Roller
          • 07-15-10
          • 184

          #5
          Originally posted by Sportsfan800
          I don't have Excel.
          Why not? There are free trials all over the internets.
          Comment
          • Sportsfan800
            SBR Hustler
            • 12-03-09
            • 64

            #6
            Originally posted by sharpcat


            Where are you storing your database? Most of the functions of Excel can be executed in Numbers on a mac.
            I'm storing my database in a Microsoft Works Spreadsheet.
            Comment
            • Flight
              Restricted User
              • 01-28-09
              • 1979

              #7
              Open Office has a good spreadsheet alternative for free download.

              You need to learn a programming language. I use C# because it has a great API and library. For beginners, Python seems to be the consensus on the forum. Python also has a rich library for doing things like interacting with web data and parsing XML.

              Based on your description, you would be crazy not to automate this. I've automated everything I can from gathering web data to analyzing teams and players, and it still takes me about 10 minutes per game/prop to go analyze.

              One last comment: don't bet using the results of your model. (At least not for a long time, and dont' use MLB. Find a smaller league to bet on)
              Comment
              • That Foreign Guy
                SBR Sharp
                • 07-18-10
                • 432

                #8
                Open office spreadsheet is basically excel - if I didn't have excel thanks to work I'd still be using it.
                Comment
                • uva3021
                  SBR Wise Guy
                  • 03-01-07
                  • 537

                  #9
                  Usiing the XR formula:
                  Code:
                  XR – Extrapolated Runs = (.50 × 1B) + (.72 × 2B) + (1.04 × 3B) + (1.44 ×  HR) + (.34 × (HP+TBB−IBB)) + (.25 × IBB)+ (.18 × SB) + (−.32 × CS) +  (−.090 × (AB − H − K)) + (−.098 x K)+ (−.37 × GIDP) + (.37 x SF) + (.04 ×  SH)
                  Structure your spreadsheet by column in this exact order of variables (1Bs in first column, 2Bs in second column, 3B in third column...)

                  add a total sum column at the end of the row of XR calculations

                  Then write a macro, I'll get you started:

                  Dim XR(13) as Double
                  XR(1) = singlex
                  XR(2) = doublex
                  XR(3) = triplex
                  ...
                  ...XR(13) = shx

                  For i = 1 to Range.Columns.Count
                  For k = 1 to Range.Rows.Count
                  tempvar = Range.Cells(k,i).Value
                  Range.Cells(k,i).Value = XR(i) * tempvar
                  Next k
                  Next i
                  ...
                  Comment
                  • Sportsfan800
                    SBR Hustler
                    • 12-03-09
                    • 64

                    #10
                    Originally posted by uva3021
                    Usiing the XR formula:
                    Code:
                    XR – Extrapolated Runs = (.50 × 1B) + (.72 × 2B) + (1.04 × 3B) + (1.44 ×  HR) + (.34 × (HP+TBB−IBB)) + (.25 × IBB)+ (.18 × SB) + (−.32 × CS) +  (−.090 × (AB − H − K)) + (−.098 x K)+ (−.37 × GIDP) + (.37 x SF) + (.04 ×  SH)
                    Structure your spreadsheet by column in this exact order of variables (1Bs in first column, 2Bs in second column, 3B in third column...)

                    add a total sum column at the end of the row of XR calculations

                    Then write a macro, I'll get you started:

                    Dim XR(13) as Double
                    XR(1) = singlex
                    XR(2) = doublex
                    XR(3) = triplex
                    ...
                    ...XR(13) = shx

                    For i = 1 to Range.Columns.Count
                    For k = 1 to Range.Rows.Count
                    tempvar = Range.Cells(k,i).Value
                    Range.Cells(k,i).Value = XR(i) * tempvar
                    Next k
                    Next i
                    ...

                    Thanks, This is what I was looking for.
                    Comment
                    • Flight
                      Restricted User
                      • 01-28-09
                      • 1979

                      #11
                      I didn't realize Works had access to VBA macros.
                      Comment
                      • pigskingenius
                        SBR Rookie
                        • 07-18-10
                        • 9

                        #12
                        you can also use google doc as you can create an excel spreadsheet on there. Other then excel like document, does anyone have an actual software they use to help them predict games?
                        Comment
                        • jfreeman
                          SBR Rookie
                          • 05-10-10
                          • 21

                          #13
                          If you're running on a mac like me, there's a handy new program that comes standard with Snow Leopard called "Automator"... its similar to a macro recorder in excel but a LOT more user friendly and it lets you automate tasks across programs on your computer. Sounds like this could be helpful IF you're on a mac... I'm currently doing a search for something similar that you could run on a PC but so far, no luck... I'll be sure to keep you updated though, sounds like a very cool project!
                          Comment
                          • uva3021
                            SBR Wise Guy
                            • 03-01-07
                            • 537

                            #14
                            well once you have written the visual basic you just have to change the properties and naming conventions to the framework of a visual basic form, i suggest getting visual studio, or just write it as an offline web page, using tables

                            if you understand program it doesn't take long before you can manifest some sort of automated game predictor, i have one for each of the major sports (includiing WNBA)
                            Comment
                            • Sportsfan800
                              SBR Hustler
                              • 12-03-09
                              • 64

                              #15
                              Originally posted by Flight
                              I didn't realize Works had access to VBA macros.
                              It doesn't, Microsoft Works is useless for sports handicapping. I downloaded Open Office and I love it. Open Office runs macros. Thanks to the poster who introduced it to me. I have a lot to learn, thanks to everyone for helping me.
                              Comment
                              • Pokerjoe
                                SBR Wise Guy
                                • 04-17-09
                                • 704

                                #16
                                Originally posted by Sportsfan800
                                I'm serious about building a succesful model to predict baseball games. I have collected all the historical data that I need from baseball reference dot com. I have found a run estimator on the internet. I'm using Jim Furtado's Extrapolated Runs Formula. I'm going through each line-up card and calculating singles, doubles, triples, etc... The problem is this is very tedious and time consuming. It takes an hour each game to do it by hand. Is there a way to automate this process? Can I have the computer calculate everything for me? The calculations are simple. Addition, subtraction, multiplication and division. Should I use team stats to speed up the process? I hesitate to do this, because I will lose some accuracy. I don't have sixteen hours a day to devote to baseball handicapping. Any suggestions?
                                I am not at all meaning to rain on your parade, but unless you come up with a really interesting, novel sabermetrics insight, you won't succeed in beating the line this way. ALL public sabermetic knowledge is incorporated into the line. Where the line varies from it, there's a reason.

                                Common sabermetric score estimations are the new vanilla in MLB sportsbetting.

                                But you can still have lots of fun trying. Enjoy.
                                Comment
                                • Flying Dutchman
                                  SBR MVP
                                  • 05-17-09
                                  • 2467

                                  #17
                                  Originally posted by Pokerjoe
                                  I am not at all meaning to rain on your parade, but unless you come up with a really interesting, novel sabermetrics insight, you won't succeed in beating the line this way. ALL public sabermetic knowledge is incorporated into the line. Where the line varies from it, there's a reason.

                                  Common sabermetric score estimations are the new vanilla in MLB sportsbetting.

                                  But you can still have lots of fun trying. Enjoy.
                                  the very reason I don't do MLB.

                                  Comment
                                  • PRC
                                    SBR Wise Guy
                                    • 10-22-09
                                    • 576

                                    #18
                                    Ive been making money all year with sabr stuff. I dont think its all priced in quite yet.
                                    Comment
                                    • Pokerjoe
                                      SBR Wise Guy
                                      • 04-17-09
                                      • 704

                                      #19
                                      Originally posted by PRC
                                      Ive been making money all year with sabr stuff. I dont think its all priced in quite yet.
                                      This statement is too vague to be judged.

                                      MLB doesn't run all "year", so you probably mean "season."
                                      No one wins "all" season, everyone has losing days, so you probably don't mean "you've been making money all year", you probably mean that "you're ahead for the season."

                                      "With sabr stuff" can mean just about anything. "Sabr stuff" is a huge range. And further, just because you've been winning doesn't mean it's because of "sabr stuff." If you go to a race track and bet all the horses in red silks and make a profit, you didn't win because you bet all the horses in red silks. Such "winning" isn't causal, it's coincidental.

                                      If a hundred guys try to beat MLB for a season with "sabr stuff," a few will end the year ahead. If a hundred guys try to beat MLB for a season without "sabr stuff," a few will end the year ahead.

                                      And anyway, "winning," if for only a little bit, or for only a few hundred bets, can mean just about nothing. Up a dollar after a thousand bets? Winner. Up a thousand after one bet? Winner. For the year.

                                      And LOL, this response reads a little bitchy, so I apologize. But my point is right: your statement, as it stands, isn't evidence of anything.
                                      Comment
                                      • Sportsfan800
                                        SBR Hustler
                                        • 12-03-09
                                        • 64

                                        #20
                                        Originally posted by Pokerjoe
                                        I am not at all meaning to rain on your parade, but unless you come up with a really interesting, novel sabermetrics insight, you won't succeed in beating the line this way. ALL public sabermetic knowledge is incorporated into the line. Where the line varies from it, there's a reason.

                                        Common sabermetric score estimations are the new vanilla in MLB sportsbetting.

                                        But you can still have lots of fun trying. Enjoy.
                                        I'm not doing this to have fun. I'm running this formula to have an edge. Have you tried using the Extrapolated Runs formula? How do you know it won't give me an edge? Past experiences or are you guessing? I know it is available on the internet for the public to find. Does this automatically make the formula a waste of time? Is it better to guesstimate what a line should be?
                                        Comment
                                        • Optional
                                          Administrator
                                          • 06-10-10
                                          • 61434

                                          #21
                                          Don't get frustrated at the messenger. I'm green and you even sound bright green to me.

                                          If you are as serious as you sound, what Pokerjoe is saying will be self evident to you pretty soon.

                                          And that doesn't mean you are doing the wrong thing here. Most of us have to put in this work ourselves to really understand/believe it's actually going to take 'something' more than just following the formulas.
                                          .
                                          Comment
                                          • Flight
                                            Restricted User
                                            • 01-28-09
                                            • 1979

                                            #22
                                            Just don't bet the model before you test it.

                                            It would be useful to read Justin7's book. There is a chapter focused on MLB run estimation.
                                            Comment
                                            SBR Contests
                                            Collapse
                                            Top-Rated US Sportsbooks
                                            Collapse
                                            Working...