Help. Importing data into Excel and writing formulas, coding

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • demens
    SBR MVP
    • 10-22-10
    • 2785

    #1
    Help. Importing data into Excel and writing formulas, coding
    I need some advice and help.

    I'm working on a few methods of developing point spreads and i'm looking for a way to use my time more efficiently.

    I need help on the following topics:
    Excel or OpenOffice Math or A program in C.

    Basically to run all the individual game though the formulas is insanely time consuming. All i do is plug numbers into different things game after game. All the numbers originate from the same place and i know there has to exist a way to set this up so all the work is done for me automatically. Maybe Excel is not capable but i think it might be. If its not, i used to know C but i haven't used it in 4 years so i'm very rusty (to the point of not remembering anything), but with some help maybe i could write a program that does what i need.

    These are more or less the steps i'd like to do.
    1) Import a table into Excel from a Website automatically. Not sure how this is done but i think it should be possible. Its not super important because i can paste a table in there manually each day, not a big deal.

    2) Import a schedule into excel. Again if its too hard i can do it manually.

    3) Now based on the schedule, i want excel to pick out the team that matches and i want it to pick out the boxes in the table i use for calulations and plug those into my questions.

    As a result, all i'd need to do i open excel. update the data from the web (automatically) and it would spit out the lines for each game on the day. Worst case i'd need to manually paste in the schedule and table but i but the rest to be done automatically.

    Sooo, who wants to help?

    1st question to get out of the way since i have OO, is this even worth trying or do i need Excel?
  • jgilmartin
    SBR MVP
    • 03-31-09
    • 1119

    #2
    I don't have OpenOffice, but it appears it does have a Web Query function similar to Excel:


    You should be able to obtain the schedule with that method also.

    #3 is a little unclear to me (are you only looking to run the calculations based on whether or not certain teams are playing? If so, you could do it with IF statements)
    Comment
    • demens
      SBR MVP
      • 10-22-10
      • 2785

      #3
      Originally posted by jgilmartin
      I don't have OpenOffice, but it appears it does have a Web Query function similar to Excel:


      You should be able to obtain the schedule with that method also.

      #3 is a little unclear to me (are you only looking to run the calculations based on whether or not certain teams are playing? If so, you could do it with IF statements)
      For #3.

      To make it simple lets say i have a table with 10 rows and 10 columns. So thats 10 teams and 9 rows of data.

      The schedule has 1 game its between team 1 and 2.

      So i want a function (sort of) that see's that its team 1, goes and looks up the table. What row 1 column 4 is, plugs into lets say Cell A5, column 6 into A6, column 9 into A7. Does the same for team 2 garbing date from row 2.

      Then i have functions ready to use the data that was just copied into cells A5,6,7 and B5,6,7 and do some magic with that. I have a general idea on how it handle it from there (although my view might be the sloppy approach taking the long way using like 5 functions to get it done rather then an advanced (but simple) one that does it all at once, but whatever, i'll worry about about when the time comes).

      From what i know this all sounds pretty doable. Just have to research more. I figured IF statements would have to be a part of that. Never used these in Excel, but i've seen example, not sure if it'll work with OO.

      Anyway, back to researching. If you have more advice/ideas feel free to add.
      Comment
      • mrtomk
        SBR High Roller
        • 02-24-11
        • 105

        #4
        Do you mean "So thats 10 teams and 9 COLUMNS of data"? As your eg describes it like that. If so, it sounds like you need a VLOOKUP

        eg =VLOOKUP("Team 1", range of your table, the number of columns across you need to look,0)

        If the team names are in a row, with data in rows below, use an HLOOKUP instead.
        Comment
        • demens
          SBR MVP
          • 10-22-10
          • 2785

          #5
          I'm making some progress with step 1. Its simple but there is a problem it does not want to accept the URLm OK button doesn't light up. It works fine if i save the html file on my comp and the tables look great and clean. Just need to go directly from the internet.

          FIXED!

          This is why i used to hate programming, because more often then not the mistake is something so beyond stupid you'd never even think of checking for it. If i select the file saved to the desktop it works right away. If i paste the URL the OK button does not light up....until i hit enter on the URL, wait for about a minute or so for it to look up the site and get the tables from it....THEN the Ok button works. Step 1 is done. Onto the schedule.
          Last edited by demens; 04-05-11, 01:08 PM.
          Comment
          • demens
            SBR MVP
            • 10-22-10
            • 2785

            #6
            Originally posted by mrtomk
            Do you mean "So thats 10 teams and 9 COLUMNS of data"? As your eg describes it like that. If so, it sounds like you need a VLOOKUP

            eg =VLOOKUP("Team 1", range of your table, the number of columns across you need to look,0)

            If the team names are in a row, with data in rows below, use an HLOOKUP instead.
            Nice, i've seen that VLOOKUP command in the link from the previous poster. Will work on that once i get the data to import properly.
            Comment
            • demens
              SBR MVP
              • 10-22-10
              • 2785

              #7
              OK, VLOOKUP works very nice.

              I just need to think of a nice clean way to structure this whole thing.

              It seems like there is no need for IF statements so far, VLOOKUP seems to be doing the job i need.

              There is a small problem however. The best schedule i found has certain team names spelled differently then they are in the table. I Dont want to spell out team names in VLOOKUP("team name"), that would defeat the purpose of what i'm trying to do. Using the Cell# for where the team name is works just fine. Except for 2 teams. L.A. Lakers and L.A. Clippers. In need that to be changed to LA Lakers and LA Clippers.

              Here is an example of how i'm thinking of structuring this thing so far. I feels sloppy, like i'm doing certain steps twice so if you guys have ideas on how to clean it up please let me know.

              Table with stats:sheet1 (A1-A5),(B1-B5),(C1-C5). It has teamname, PPG, OPPG
              Atlanta 95 95
              Boston 95 90
              LA Lakers 100 90
              NY 99 100
              Washington 85 90

              Schedule:sheet2 (A1-A26), best schedule i could find has teams playing each other listed in a column not row.
              So Atl vs NY and NJ vs LA are playing today:
              NY
              Atlanta
              L.A. Lakers
              NJ

              ------------------------
              Now let say i will use this equation (i wont but its simple enough to test).
              (TeamAppg+TeamBoppg)/2
              ------------------------
              So technically i can start doing this right now without any delay, but i'm gonna take it step by step doing it the slow and messy way 1st and then see if i can combine a few steps and clean up.
              -----------------------
              1st i'm going to added another 2 columns to sheet2. It will be for teams ppg and oppg
              Starting in B1:
              =VLOOKUP(A1;sheet1.A1:C5;2,0) /////that should see A1=NY, go to sheet1, find "NY" in table A1 to C5, and give me whats in the 2nd column in row NY. Right? Seems to work so far. This gives us NYs ppg
              in C1:
              =VLOOKUP(A1;sheet1.A1:C5;3,0)/////Boom, we have NYs Oppg
              in B2:
              =VLOOKUP(A2;sheet1.A1:C5;2,0)///Atlantas ppg
              in C2:
              =VLOOKUP(A2;sheet1.A1:C5;3,0)////Atlants oppg
              ------------------------
              Now my sheet2 should be looking like this (A1-A26) and (B1-B26) and (C1-C26)
              NY 99 100
              Atlanta 95 95
              L.A. Lakers
              NJ

              -----------------------
              From there i can do my equation in column E(skip one)
              =(B1+C2)/2 and i get NYs expected score in this game
              =(C1+B/2 i get Atls score
              -----------------------
              Now my sheet2 should be looking like this (A1-A26) and (B1-B26) and (C1-C26) and (E1-E26)
              NY 99 100 97
              Atlanta 95 95 97.5
              L.A. Lakers
              NJ

              ----------------------
              I could cut out the middle step and put all if into the equation right from the start. That works too. Then the next step would be to put those result into yet another equation and then another and then another.
              ----------------------
              2 questions.
              1 about making it cleaner. I've been thinking about it and it seems like i will have to run whatever equations i used for each game. Meaning if i have 5 games i'll have the same equations written 5 times next to each game. Not a big deal since it'll only be done once and do the calculations automatically from there. I just wanna make sure there is no way to write it just once and have it calc game1, save the result, calc game2 save the result and so on. I guess something will this would only work if i wrote a program and called the equation as a function then saved the results in a table somewhere. So ok, maybe i wont bother with that.

              2. About the 2nd game. How do i get around the LA vs L.A. thing. I'm thinking that if i were to copy the schedule yet again to another page instead of doing the VLOOKUP right next to the imported one, i can pre face each Cell with an IF command like (just guessing) IF(A1=LA Lakers;L.A. Lakers(IF(A1=LA Clippers;L.A. Clippers);A1). Something like that. Feels redundant and sloppy but i think it'll work.


              Anyway....what do you guys think so the process so far.

              EDIT:
              I feel like i might be better off doing each of the steps individually even if they are redundant. There is just a TON of steps involved in the calculations later and i feel like if i squeez them all into 1 equation rather then spread them out over a few steps i leave my self open to a silly mistake that will be hard to hunt down later on.
              Last edited by demens; 04-05-11, 03:40 PM.
              Comment
              • demens
                SBR MVP
                • 10-22-10
                • 2785

                #8
                Well, IF statement is not working for me. It works when using #s but not words.

                n/m, fixed by using "xxx"
                Comment
                • mrtomk
                  SBR High Roller
                  • 02-24-11
                  • 105

                  #9
                  Similar to what you suggested yourself, if you are unable to just have the LA in your table as L.A. to satisfy the imported schedule, you could always replace your initial vlookups with something a little contrived, like this:

                  =IF(A1="LA Lakers",VLOOKUP("L.A. Lakers",Sheet1!$A$1:$C$4,2,0),IF(A1="LA Clippers",VLOOKUP("L.A. Clippers",Sheet1!$A$1:$C$5,2,0),VLOOKUP( A1,Sheet1!$A$1:$C$5,2,0)))
                  Last edited by mrtomk; 04-05-11, 04:22 PM.
                  Comment
                  • mrtomk
                    SBR High Roller
                    • 02-24-11
                    • 105

                    #10
                    Meanwhile, I dont really understand your explanation of your first question...
                    Comment
                    • demens
                      SBR MVP
                      • 10-22-10
                      • 2785

                      #11
                      That question isn't that important. Its just that i have the same formulas pasted next to each game. I consider that to be sloppy and waste space so to speak. I'd prefer the formulas be written just once and the different sets of numbers just run through it. But i realize its probably not possible and even if it is it might be too much work especially considering everything is working beautifully so far.

                      I used the following to fix the LA problem:
                      =IF(Schedule.C2="l.a. lakers";"LA Lakers";IF(Schedule.C2="l.a. clippers";"LA Clippers";Schedule.C2))
                      Comment
                      • mrtomk
                        SBR High Roller
                        • 02-24-11
                        • 105

                        #12
                        Surely you need the formulas against each game to account for the different teams in the cells to the left? You could always record a macro to carry out your formulas and then Copy & Paste Special... Values. So you are left with just the values of each game.
                        Last edited by mrtomk; 04-05-11, 05:54 PM.
                        Comment
                        • Lorinda
                          SBR Rookie
                          • 06-04-07
                          • 18

                          #13
                          Functions that can help you clean stuff up:

                          RIGHT
                          VALUE
                          FIND
                          LEFT
                          CELL
                          INDIRECT
                          ADDRESS

                          I use most of these in cleaning up strings, probably easier if you find some examples of what they do online rather than me explain technical stuff badly.
                          Comment
                          • Lorinda
                            SBR Rookie
                            • 06-04-07
                            • 18

                            #14
                            Ctrl-F

                            "Find and Replace"

                            Put L.A. in the "Find" box and "LA" in the "Replace box" and "Replace All"
                            Comment
                            • demens
                              SBR MVP
                              • 10-22-10
                              • 2785

                              #15
                              Originally posted by Lorinda
                              Ctrl-F

                              "Find and Replace"

                              Put L.A. in the "Find" box and "LA" in the "Replace box" and "Replace All"
                              Thanks for the advice. I fixed the LA issue with an if statement. No problems anymore.

                              Find and Replace was very useful as well when i was Copying/Dragging Formulas and it kept incrementing things i did not want it to increment. When i got sick of fixing the mistakes i later found out i should have just used $ to keep it constant.
                              Comment
                              • demens
                                SBR MVP
                                • 10-22-10
                                • 2785

                                #16
                                There is a VERY annoying problem.

                                The schedule is different every day, so 1 day it can import 4 games (A1-A8) and another 2 (A1-A4).

                                The problem is that all the other sheets start by copying the schedule and looking up the team names in the table and going from there. So if on day 1 i had those 4 games, the 2nd sheet looking something like =Schedule.A1. It was fine.

                                Now on a 2nd day, only 2 games. So the 1st 4 cells looks just fine. Still =Schedule.A1....A2 and so on. The problem now is that starting from the 5th cell it says REF. So if i pasted or typed a team name into A5 on the schedule sheet it would not be copied over to the 2nd sheet because that cell is no longer = Schedule.A5 its REF whatever error.

                                If i use Schedule.$A$5 it still does not fix the problem. For example if i did that, i could type in a team name in A5 on the schedule sheet and it will be copied over, BUT if on the next game instead of 2 games there is just 1 game. It'd copy the 1st 2 cells (A1-A2) just fine. But now the 3rd cell would actually be that A5 cell, it'd be changed to $A$3 instead.

                                It happens like this because if the table is lets say 10 rows, but then a new table is 6 row, everything that was under 10 the day before (starting with cell 11) gets shifter up to the 1st cell after the new table (to cell 7).

                                That is a problem. How can i fix this?
                                Comment
                                • mrtomk
                                  SBR High Roller
                                  • 02-24-11
                                  • 105

                                  #17
                                  So the schedule from day 2 is taking the place of the schedule for day 1, in the same cells.

                                  I might be wrong but it sounds like this is happening because you are deleting the rows/cells, instead of just emptying them. I.e. instead of highlighting the cells and pressing 'delete', you are right clicking and pressing delete, so the cells are actually being removed from the sheet entirely, therefore, your formulas are looking for cells that no longer exist in your sheet.
                                  Comment
                                  • demens
                                    SBR MVP
                                    • 10-22-10
                                    • 2785

                                    #18
                                    Originally posted by mrtomk
                                    So the schedule from day 2 is taking the place of the schedule for day 1, in the same cells.

                                    I might be wrong but it sounds like this is happening because you are deleting the rows/cells, instead of just emptying them. I.e. instead of highlighting the cells and pressing 'delete', you are right clicking and pressing delete, so the cells are actually being removed from the sheet entirely, therefore, your formulas are looking for cells that no longer exist in your sheet.
                                    Yes, thats sounds like exactly whats happening except I'm not right clicking anything, it seems to be happening this way automatically when the new schedule is updated.

                                    I found a sloppy temp fix to it by importing the schedule about 20 cells lower and manually copy and pasting it into cells A1-A26 (or however many games there are). Its not a lot of work but still it'd be nice if it just did that automatically, i dont see why it can't.
                                    Comment
                                    • mrtomk
                                      SBR High Roller
                                      • 02-24-11
                                      • 105

                                      #19
                                      Im sure there is a way. Dont think Ill be able to help as I dont know much about programming and dont know how you are importing the schedule specifically. Regards.
                                      Last edited by mrtomk; 04-07-11, 12:18 PM.
                                      Comment
                                      • demens
                                        SBR MVP
                                        • 10-22-10
                                        • 2785

                                        #20
                                        Importing directly from a website.

                                        I think i found a Fix. It involved using =INDEX(range,row). Instead of just =cell.

                                        Now the range automatically changes to whatever the table is. Which works. But this isn't 100% ideal with what i would like to have. Its 99%, but i would prefer the range be set to a constant and not change at all not matter what the table size it (it would never be more then the range). This way i could manually add more teams to the schedule if i wanted to cap a game thats 2 or more days away for example. As things stand i can't do it because it falls outside of the table range. I COULD just type it in instead of one of the games within the range.

                                        But whatever, its not a big deal at all. I think i'm being too much of a perfectionist at this point.
                                        Comment
                                        SBR Contests
                                        Collapse
                                        Top-Rated US Sportsbooks
                                        Collapse
                                        Working...