After downloading stats from a website using the external data, new data query, everything gets saved into cells. How can I maintain those values in the existing cells if I download the next weeks stats. This causes the values to change in the existing cell to what you download.
new data query question excel
Collapse
X
-
FormulawizRestricted User
- 01-12-09
- 1589
#1new data query question excelTags: None -
djiddish98SBR Sharp
- 11-13-09
- 345
#2I think the data query's function by design is to refresh the data, but keep the cell references the same.
Are you looking to keep some cells constant from prior weeks but update others? Or keep the original set in one place, and then grab that updated number?
Your best bet might be a macro that runs through, grabs the data you want to keep, puts it in a separate sheet, and then runs the refresh.Comment -
FormulawizRestricted User
- 01-12-09
- 1589
#3I would like to keep the cells with last weeks stats the same and then use the new query for the upcoming week.Comment -
djiddish98SBR Sharp
- 11-13-09
- 345
#4I would recommend going the macro route, instead of loading up a query for each week. I don't know how queries affect excel file size (probably minimal), but it seems unnecessary.
The process would look like this.
1)Query week 1
2) Copy week 1 stats into a separate sheet, marking them as week 1 stats
3) modify your query to grab week 2
4) copy week 2
5) rinse and repeat
If you're un-familiar with macros and programming, start with the macro recorder, create a query, and see what it's doing. Then,try replacing the hard-coded macro-recorder code with variables and go from there.Comment -
TomGSBR Wise Guy
- 10-29-07
- 500
#5If it's just a once a week thing, don't bother with a macro. Just copy the results -> Paste Special Values into a separate tab. Now you have that week hard coded and saved while the web query still set to update with new data.Comment -
FormulawizRestricted User
- 01-12-09
- 1589
#6I would recommend going the macro route, instead of loading up a query for each week. I don't know how queries affect excel file size (probably minimal), but it seems unnecessary.
The process would look like this.
1)Query week 1
2) Copy week 1 stats into a separate sheet, marking them as week 1 stats
3) modify your query to grab week 2
4) copy week 2
5) rinse and repeat
If you're un-familiar with macros and programming, start with the macro recorder, create a query, and see what it's doing. Then,try replacing the hard-coded macro-recorder code with variables and go from there.Comment -
djiddish98SBR Sharp
- 11-13-09
- 345
#7
Originally posted by FormulaWhizI do this for football but you cant do it for basketball there are too many games. I still would like to know how to do it if you look at my last post.
When I setup my macro, I would have the links to the data, query each link, and then pull the data into a separate sheet for each query. Probably took about 30 seconds on a crap computer for a full night of NBA games.Comment -
FormulawizRestricted User
- 01-12-09
- 1589
#8Fair point - I hate manually doing anything, so I'd rather waste an hour setting something up to save 5 seconds.
I started this setup for basketball, so I'm not sure why you're saying there are too many games. Care to elaborate?
When I setup my macro, I would have the links to the data, query each link, and then pull the data into a separate sheet for each query. Probably took about 30 seconds on a crap computer for a full night of NBA games.Comment -
FormulawizRestricted User
- 01-12-09
- 1589
#9here is an example spreadsheet. As you can see the the cfb lines are taken from wagertracker for week 14. For example if we look at column F under sheet 1, we have all the lines. If we now change the lines page to week 15, all the values will change in shhet 1, cloumn F. How can we keep the lines in week 14 the same even if we change to week 15 without doing any programming, just by formula.Attached FilesComment -
djiddish98SBR Sharp
- 11-13-09
- 345
#10You'll have to copy and paste if you don't want to do anything programming based.
I believe you can't reference two different URLs using 1 query, so you can't plug in partial week 15 info with some week 14 info staying.
If you start stacking queries, you'll have to adjust your formula for the team spread every time to reference the new cells.
I slapped some code together to make the copying and pasting easier, but SBR doesn't allow for .xlsm attachment uploads. Here's the code:
Code:Sub getSpread() Set Fill = Sheets("Sheet1").Range("a2") While IsEmpty(Fill) = False Set Fill = Fill.Offset(1, 0) Wend i = 0 While i < 2 Set check = Sheets("Lines").Range("f9") While IsEmpty(check) = False Fill.Value = check.Value Fill.Offset(0, 1).Value = check.Offset(1, 0).Value Fill.Offset(0, 3).Value = check.Offset(2, 0).Value Fill.Offset(0, 5).Value = check.Offset(2, 2).Value Set check = check.Offset(4, 0) Set Fill = Fill.Offset(1, 0) Wend Set check = Sheets("Lines").Range("k9") i = i + 1 Wend End Sub
Good luck!Comment -
djiddish98SBR Sharp
- 11-13-09
- 345
#11As a note: all this macro does is start with the first game (presumed to be date in Lines tab cell f9), and cycle through till there's no game.
Then it moves over to the second column of games, since wagertracker doesn't present their data in a straight line, starting at cell k9.
It copies the date, teams and spread for the home team over to sheet1.
FYI - it starts with the first empty cell on Sheet1, so you don't over-write any old spreads - if you ran the macro a lot for one week, you'd end up duplicating your spreads every time.Comment
SBR Contests
Collapse
Top-Rated US Sportsbooks
Collapse
#1 BetMGM
4.8/5 BetMGM Bonus Code
#2 FanDuel
4.8/5 FanDuel Promo Code
#3 Caesars
4.8/5 Caesars Promo Code
#4 DraftKings
4.7/5 DraftKings Promo Code
#5 Fanatics
#6 bet365
4.7/5 bet365 Bonus Code
#7 Hard Rock
4.1/5 Hard Rock Bet Promo Code
#8 BetRivers
4.1/5 BetRivers Bonus Code