Excel is my friend

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • subs
    SBR MVP
    • 04-30-10
    • 1412

    #1
    Excel is my friend
    Hi every 1. sorry for the excel n00bness here. but i been going round and round with this 1.

    ok so i want to break the below cell into HOME TEAM.

    296 Tennessee Martin at 2 Ohio St


    sometimes they are separated with an "at" or a "vs". i tried to do it in 2 columns, it gives me "VALUE!" but i can't seem to work with that.

    =RIGHT(B15,LEN(B15)-SEARCH("at",B15,1)-3) how can you get it to search for "at" OR "vs"
    even better would be

    "get the text after the sec string of numbers +1"

    anyway i bet a bunch of you guys have this worksheet already done, be great if i could get a look at how 1 or 2 of you would have split these cells up.

    many thanks
  • c4yrslf12
    SBR High Roller
    • 11-01-10
    • 139

    #2
    home team always listed second?
    Comment
    • subs
      SBR MVP
      • 04-30-10
      • 1412

      #3
      I think so....
      Last edited by subs; 12-26-10, 07:05 PM.
      Comment
      • lovesbaseball1
        SBR Rookie
        • 12-07-10
        • 18

        #4
        This will work, it tests whether there is an error finding " at ", if there is, it searches for " vs " instead. notice I added spaces around " at " and " vs " to be safe in case a team name includes "at"

        =RIGHT(B15,LEN(B15)-SEARCH(IF(ISERR(FIND(" at ", B15, 1)), " vs ", " at "),B15,1)-5)

        Originally posted by subs
        Hi every 1. sorry for the excel n00bness here. but i been going round and round with this 1.

        ok so i want to break the below cell into HOME TEAM.

        296 Tennessee Martin at 2 Ohio St


        sometimes they are separated with an "at" or a "vs". i tried to do it in 2 columns, it gives me "VALUE!" but i can't seem to work with that.

        =RIGHT(B15,LEN(B15)-SEARCH("at",B15,1)-3) how can you get it to search for "at" OR "vs"
        even better would be

        "get the text after the sec string of numbers +1"

        anyway i bet a bunch of you guys have this worksheet already done, be great if i could get a look at how 1 or 2 of you would have split these cells up.

        many thanks
        Comment
        • subs
          SBR MVP
          • 04-30-10
          • 1412

          #5
          love that IFERR and the spaces

          thanks thanks
          Comment
          • subs
            SBR MVP
            • 04-30-10
            • 1412

            #6
            NOOOOO, they throw in a few that are switched around!!!! Grrrrr.

            WTF? can any1 suggest a plan of action. i mean they don't even call the teams the same thing so i can't do a simple comparison.

            i'm just trying to get data from a CBB site to talk to pinny in my excel. any plan of action/resources/tips is very much appreciated.

            thank you
            Comment
            • djiddish98
              SBR Sharp
              • 11-13-09
              • 345

              #7
              You could add a column that checks to see if it's " at " or " vs " - then, in the next column, if it's " at " use the right function, if it's " vs ", use a left function (I'm assuming " vs " indicates first team as home team). This would simplify the function a bit, at the expense of having more columns of data.

              Otherwise, you can change the order of the formula, which will check first to see if it can find " at ", and if not, take the first team as the home team.

              Something like IF(ISERR(FIND(" at ",b15)), LEFT(b15,find(" vs ",b15)),right(b15,len(b15)-find(" at ",b15))

              I just wrote that in this message box, so the code doesn't likely work, but it should give you an idea.
              Comment
              • subs
                SBR MVP
                • 04-30-10
                • 1412

                #8
                thanks djiddish98

                i like the idea and the code example, but sadly no, the order seems random right now. i mean sometimes it is "vs" and normal, away team 1st.

                does any1 know why?

                the only thing i can think of is to load in sbr odds and then try to match them up. something like a lookup table where each team is spelled out. i need to do this anyway for the lines...

                much to learn...
                Comment
                • Peregrine Stoop
                  SBR Wise Guy
                  • 10-23-09
                  • 869

                  #9
                  why you doing this anyway? Probably not a very efficient use of time.
                  Comment
                  • subs
                    SBR MVP
                    • 04-30-10
                    • 1412

                    #10
                    well i was wondering if there were any profitable subsets with this or not? i will need to track it for a while, right?

                    there are many subsets that i can think of, where being more or less profitable, and the implications for staking strategy, can hopefully make some money. i need to compare to WA lines and totals. By halfs and quarters is a bit beyond me ATM.

                    do you think that this is a waste of time? IF you KNOW that this is a waste of time then cool, but otherwise i would like to know.

                    it is only 1 of 2 projects for me right now, the other is making good push charts for teasers in all 4 sports.
                    Comment
                    • Ganchrow
                      SBR Hall of Famer
                      • 08-28-05
                      • 5011

                      #11
                      It was situations like that spurred the gods to create "Regular Expressions". Regular expressions represent some of the useful features of languages such as Perl, PHP, Python, and many others, but VBA actually has its own (slow & limited) regular expression support through VBScript.

                      To create a simple regular expression "replace" function for use in an Excel spreadsheet you can use the following VBA function:

                      (ALT-11 to bring up the VB Editor, right click the desired workbook, select insert module and add the following lines of code).

                      Code:
                      ' RR: Simple Regular Expression Replace Wrapper Function for VBA
                      Public Function RR(ByVal sText As String, _
                                          ByVal sPattern As String, _
                                          Optional ByVal sReplacement As String = "", _
                                          Optional ByVal bIgnoreCase As Boolean = False, _
                                          Optional ByVal bGlobal As Boolean = False _
                      ) As String
                              Dim oRGX As Object
                              Set oRGX = CreateObject("VBScript.RegExp")
                              oRGX.Pattern = sPattern
                              oRGX.IgnoreCase = bIgnoreCase
                              oRGX.Global = bGlobal
                              RR = oRGX.Replace(sText, sReplacement)
                      End Function
                      And then assuming the cell of interest were A1 and contained the string "296 Tennessee Martin at 2 Ohio St", you might use any of the following formulas to yield the desired result:
                      • =rr(A1,"^.*?\s(at|vs)\s+[0-9]*\s*")
                        Which means remove (or technically, replace with an empty string) everything in the string up until and including the first appearance of either " at " or " vs ", optionally followed by additional whitespace, followed by 0 or more digits, optionally followed by more whitespace. If no such match occurs, return the entire string.
                      • =rr(A1,"^.*\s(at|vs)\s+[0-9]*\s*")
                        Which means remove everything in the string up until and including the last appearance of either " at " or " vs ", optionally followed by additional whitespace, followed by 0 or more digits, optionally followed by more whitespace. If no such match occurs, return the entire string.
                      • =rr(A1,"^\s*[0-9]+.*?\s[0-9]+\s+")
                        Which means if the string starts with a 1 or more digits surrounded by whitespace (leading whitespace being optional) then remove everything up to and including the next instance of a 1 or more digits surrounded by whitespace. If no such match occurs, return the entire string.
                      • =rr(A1,"^.*\s[0-9]+\s+")
                        Which means, Remove everything in the string up until and including the last appearance of one or more digits surrounded by white space. If no such match occurs, return the entire string.


                      So in each of these cases the string "296 Tennessee Martin at 2 Ohio St" would yield the resultant string "Ohio St".

                      Disclaimer: This is for illustrative purposes only ... there are better and faster ways of handling this.
                      Comment
                      • Ganchrow
                        SBR Hall of Famer
                        • 08-28-05
                        • 5011

                        #12
                        And if the data source could not be consistently counted upon to display the away team prior to the home, just as long as the first team listed were preceded by its rotation #, we could take advantage of the fact that home rot # are even and use the following VBA function in Excel:

                        Code:
                        Public Function ExtractHomeByRegEx(ByVal sText As String) As String
                            Dim oRGX As Object
                            Set oRGX = CreateObject("VBScript.RegExp")
                            oRGX.Pattern = "(?:^|\s)\d*[02468]\s+(.+?)\s*(?:(?:vs|at|\d+)\s|$)"
                            ExtractHomeByRegEx = oRGX.Execute(sText).Item(0).SubMatches.Item(0)
                        End Function
                        Then:
                        =ExtractHomeByRegEx("295 Tennessee Martin vs 296 Ohio St")
                        =ExtractHomeByRegEx("296 Ohio St vs 295 Tennessee Martin")
                        Would properly yield "Ohio St" in both cases.

                        Creating a corresponding ExtractAwayByRegEx() function and/or a general case ExtractTeamsByRegEx() array function should serve as good VBA practice.
                        Comment
                        • subs
                          SBR MVP
                          • 04-30-10
                          • 1412

                          #13
                          Hello Granchow

                          thank you for your help, you're obviously on another level completely to me... i've made sure to read many many of your posts and use your Simple VBA Sports Betting Functions Template for Excel

                          Actually, i used 2 columns each, thanks to lovesbaseball1, to get 1st team and 2nd team. just replacing vs with at would have done it. but your looking at strings and "replace" them is too awesome, i will have to use it just to see it working.

                          i believe i will be using an enormously inefficient answer to my named differently teams and switched around (home 2nd/1st) problem.

                          do you mind me asking you on your preferred, fairly easily understandable, method for smoothing push chart data; do i need Cooley-Tukey or will something easier suffice?

                          i am afraid we all need (i mean me obviously) a more accurate HPC, a bunch of new functions, a more complete education, help with questions etc etc.

                          Happy New Year Every1 - a toast...
                          Last edited by subs; 12-31-10, 04:49 AM.
                          Comment
                          • subs
                            SBR MVP
                            • 04-30-10
                            • 1412

                            #14
                            Originally posted by Ganchrow
                            And if the data source could not be consistently counted upon to display the away team prior to the home, just as long as the first team listed were preceded by its rotation #, we could take advantage of the fact that home rot # are even and use the following VBA function in Excel:

                            Code:
                            Public Function ExtractHomeByRegEx(ByVal sText As String) As String
                                Dim oRGX As Object
                                Set oRGX = CreateObject("VBScript.RegExp")
                                oRGX.Pattern = "(?:^|\s)\d*[02468]\s+(.+?)\s*(?:(?:vs|at|\d+)\s|$)"
                                ExtractHomeByRegEx = oRGX.Execute(sText).Item(0).SubMatches.Item(0)
                            End Function
                            Then:
                            =ExtractHomeByRegEx("295 Tennessee Martin vs 296 Ohio St")
                            =ExtractHomeByRegEx("296 Ohio St vs 295 Tennessee Martin")
                            Would properly yield "Ohio St" in both cases.

                            Creating a corresponding ExtractAwayByRegEx() function and/or a general case ExtractTeamsByRegEx() array function should serve as good VBA practice.
                            this is going to be excellent practice for me, and i'm very much looking forward to it.

                            although, sadly again, it is not the rotation number but a power ranking. i'm going to do my lists of the names for the teams, kenprom and sbr odds, when they are different.

                            if any1 wants the list i will post it.
                            Last edited by subs; 12-31-10, 05:11 AM.
                            Comment
                            • pedro803
                              SBR Sharp
                              • 01-02-10
                              • 309

                              #15
                              Wow! a Ganchrow siting!! Good to see you around checking on us think tankers!

                              Happy New Year Everybody!!
                              Comment
                              • Pokerjoe
                                SBR Wise Guy
                                • 04-17-09
                                • 704

                                #16
                                OP, did you know that some sites list "vs" instead of "at" because the game is being played at a neutral site?
                                Comment
                                • subs
                                  SBR MVP
                                  • 04-30-10
                                  • 1412

                                  #17
                                  thanks Joe
                                  Comment
                                  SBR Contests
                                  Collapse
                                  Top-Rated US Sportsbooks
                                  Collapse
                                  Working...