Question regarding Betting Data Transformation in Excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • nash13
    SBR MVP
    • 01-21-14
    • 1122

    #1
    Question regarding Betting Data Transformation in Excel
    I have a dataset from the web where the data is structured as this

    Rot VH Team 1st 2nd Final Open Close ML 2H
    717 V Evansville 27 20 47 135,5 141 2500 74,5
    718 H Louisville 43 35 78 17 22,5 -5000 8,5
    now i'd like to have an added column where the line is calculated for each team, and the totals seperated for further calculations
    a column where Evansville has -17 as Open and -22 as close and the corresponding line for Louisville. Is there an Excel formula to transform this to easier structured data?
  • Waterstpub87
    SBR MVP
    • 09-09-09
    • 4102

    #2
    Not completely sure what you are asking for.

    If you want something like

    Away Line Open Line Close Home Line Open Line Close

    Notation is as follows: Letter for columns, rows for numbers, so where you have 717 would be A2

    Away = C2
    Open= IF(LEFT(G2,3)<100,IFERROR(IF(FIND(",",G2 )>0,LEFT(G2,FIND(",",G2)-1)+0.5,-G2),G2),IFERROR(IF(FIND(",",G3)>0,(LEFT( G3,FIND(",",G3)-1)+0.5)*-1,G3),-G3))
    Close= =IF(LEFT(H2,3)<100,IFERROR(IF(FIND(",",H 2)>0,LEFT(H2,FIND(",",H2)-1)+0.5,-H2),H2),IFERROR(IF(FIND(",",H3)>0,(LEFT( H3,FIND(",",H3)-1)+0.5)*-1,H3),-H3))

    Home =C3
    Open =IF(LEFT(G2,3)<100,IFERROR(IF(FIND(",",G 2)>0,LEFT(G2,FIND(",",G2)-1)+0.5,G2),G2),IFERROR(IF(FIND(",",G3)>0 ,(LEFT(G3,FIND(",",G3)-1)+0.5)*-1,G3),G3))

    Close= IF(LEFT(H2,3)<100,IFERROR(IF(FIND(",",H2 )>0,LEFT(H2,FIND(",",H2)-1)+0.5,H2),H2),IFERROR(IF(FIND(",",H3)>0 ,(LEFT(H3,FIND(",",H3)-1)+0.5),H3),H3))
    Comment
    • Waterstpub87
      SBR MVP
      • 09-09-09
      • 4102

      #3
      Unless the numbers are always indicating a favorite when being shown, despite not have a negative sign
      Comment
      • nash13
        SBR MVP
        • 01-21-14
        • 1122

        #4
        First of all thanks a million for the Help.
        This is a typical day from the dataset:

        Date Rot VH Team 1st 2nd Final Open Close ML 2H
        1112 509 V Massachusetts 25 33 67 139,5 140,5 260 73,5
        1112 510 H Harvard 29 29 70 9 7,5 -330 3
        1112 511 V GeorgeMason 27 34 61 144,5 145,5 1750 76
        1112 512 H Louisville 24 48 72 18 19 -3500 12,5
        1112 513 V ULMonroe 31 34 65 137,5 140,5 1500 73
        1112 514 H SMU 35 48 83 18,5 17,5 -3000 9
        1112 515 V TexasState 24 33 57 134,5 134,5 135 1
        1112 516 H AirForce 33 32 65 3 3 -155 71,5
        1112 517 V CalPolySLO 30 52 82 137 134,5 350 74
        1112 518 H California 44 41 85 9,5 8,5 -450 1
        1112 519 V SanDiego 51 30 81 1 3 -155 75
        1112 520 H SanJoseState 29 35 64 139,5 137,5 135 4
        1112 521 V Yale 22 39 61 137,5 138,5 1000 72,5
        1112 522 H Wisconsin 43 46 89 13 15 -2000 4
        1112 523 V Princeton 31 44 75 137,5 143,5 300 76
        1112 524 H Butler 41 44 85 8 8 -400 2,5
        1112 525 V Pacific 31 49 80 142 140 76
        1112 526 H Stanford 53 36 89 18 18,5 6
        1112 527 N ArkPineBluff 21 36 57 145 145,5 1750 75
        1112 528 N Troy 31 50 81 18,5 18,5 -3500 7
        1112 529 V NorthDakota 31 47 78 152 154,5 175 1
        1112 530 H Hawaii 46 35 81 3,5 5 -210 81
        1112 531 V TheCitadel 47 46 93 182 187,5 7000 99,5
        1112 532 H VirginiaTech 66 66 132 24 26 -18500 9,5
        1112 533 V Monmouth 32 33 65 153 148 77
        1112 534 H SetonHall 48 27 75 16 16,5 5
        1112 535 V NebraskaOmaha 38 51 89 155 157 85,5
        1112 536 H Oklahoma 53 55 108 22 23,5 10
        1112 537 V SouthernUtah 29 40 69 162 162,5 2625 83
        1112 538 H BoiseState 42 48 90 21,5 21,5 -5500 8,5
        1112 539 V TennMartin 29 45 74 146,5 155,5 2500 82
        1112 540 H Illinois 44 33 77 18 21,5 -5000 8
        1112 541 V CalIrvine 35 48 83 139 136,5 175 74
        1112 542 H DenverU 25 44 69 4,5 5 -210 5,5
        1112 543 V E.Washington 34 35 69 157 159,5 415 82,5
        1112 544 H WashingtonU 40 39 79 10 9,5 -525 4
        1112 545 V Vermont 24 45 69 650
        1112 546 H Kentucky 36 37 73 12 12 -1000 4,5
        1112 547 V Bucknell 34 39 73 400
        1112 548 H Arkansas 56 45 101 8 9,5 -500 2
        1112 549 V MDBaltimoreCo 47 31 78 6750
        1112 550 H Arizona 56 47 103 22,5 25,5 -17500 12
        1112 551 V Howard 27 50 77 5000
        1112 552 H IndianaU 37 49 86 23,5 24,5 -12500 11
        So the lines always indicate a Dog, and pk, available stands for hcp of 0.
        I din't understand why they structure the data like this? scoresandodds does it the same way. would be a lot easier to have separate columns for totals and lines.
        for anyone interested: google sportsbookreviewsonline and scores and odds archives.
        they have the lines starting 2007, important for me were opening and closing odds.
        Comment
        SBR Contests
        Collapse
        Top-Rated US Sportsbooks
        Collapse
        Working...