Any sample code for logging into a user account for Pinnacle to get to the Dynamic Odds pages (the ones with drop-downs) rather than the static lines available to everyone?
Excel based tutorial for web scraping
Collapse
X
-
solringSBR High Roller
- 11-04-09
- 171
#141Comment -
oilcountry99SBR Wise Guy
- 08-29-10
- 707
#142heres an easy on for you guys....
In cell A1 i have 951 L.A. Dodgers in cell A2 I want to see just L.A. Dodgers
I'm wondering what formula I need in A2 to produce these results.
Also will the same formula work if i have 8 L.A. Dodgers or 25 L.A. Dodgers in cell A1
OilComment -
oilcountry99SBR Wise Guy
- 08-29-10
- 707
#143Here's another one....
In VBA how do I find a variable and delete all rows below.
I want to find tomorrows date so I used
Dim nextday As String
nextday = Format(Date + 1, "d-mmm-yy")
'Clear rows below tomorrow's date (nextday)
With Sheets("Sheet1")
.Rows(nextday.Row & ":" & .Rows.Count).Delete
End With
I know this is jacked, because I don't know how to do it......please help!!!Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#144heres an easy on for you guys....
In cell A1 i have 951 L.A. Dodgers in cell A2 I want to see just L.A. Dodgers
I'm wondering what formula I need in A2 to produce these results.
Also will the same formula work if i have 8 L.A. Dodgers or 25 L.A. Dodgers in cell A1
Oil
You will want to use either LEFT,RIGHT or Mid functionComment -
a4u2fearSBR Hall of Famer
- 01-29-10
- 8147
#145someone else posted this question in the think tank and he PMd me about it. It seems like Pinnacle recently changed their log in to have encryption so I do not know how as of now.Comment -
a4u2fearSBR Hall of Famer
- 01-29-10
- 8147
#146heres an easy on for you guys....
In cell A1 i have 951 L.A. Dodgers in cell A2 I want to see just L.A. Dodgers
I'm wondering what formula I need in A2 to produce these results.
Also will the same formula work if i have 8 L.A. Dodgers or 25 L.A. Dodgers in cell A1
Oil
You can then use the "mid(...)" function to return the string from the 5th digit of "951 L.A. Dodgers" to the end. The 5th digit is L because the 4th is the space.
You can use the "len(...)" function to find the length of "951 L.A. Dodgers" so you use the Mid function to grab the string from the 5th digit to the end of the string (told by len)Comment -
a4u2fearSBR Hall of Famer
- 01-29-10
- 8147
#147Here's another one....
In VBA how do I find a variable and delete all rows below.
I want to find tomorrows date so I used
Dim nextday As String
nextday = Format(Date + 1, "d-mmm-yy")
'Clear rows below tomorrow's date (nextday)
With Sheets("Sheet1")
.Rows(nextday.Row & ":" & .Rows.Count).Delete
End With
I know this is jacked, because I don't know how to do it......please help!!!
Application.Match(stringtosearchfor, range,0)
if you're looking in column A, start searching in A1:A100 or wahtever. If the string is found, and is must match exactly, it will return the row it is in.Comment -
a4u2fearSBR Hall of Famer
- 01-29-10
- 8147
#148Just a random thought, but most of you guys posting in here have been members of the forum for quite a few years but have very few posts. Glad I'm getting some posts out of you or at least exciting your interest.
Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#149brad, you need more loops to simplify. You basically have a (4) sets of (3) lines written for each division above. ]
The below is for metro and atlantic parts you posted
You could simplify to
for z=0 to 4 step 4
y=0
for x=1 to 4
if x=3 then
y=1
elseif x=4 then
y=4
end if
Sheets("Sheet1").Cells(lastrowsheetone + 1+z, x+1) = Sheets("Temp").Cells(findmatch + 2+z, x+y) '1st
Sheets("Sheet1").Cells(lastrowsheetone + 2+z, x+1) = Sheets("Temp").Cells(findmatch + 3+z, x+y) '2nd
Sheets("Sheet1").Cells(lastrowsheetone + 3+z, x+1) = Sheets("Temp").Cells(findmatch + 4+z, x+y) '3rd
Next
I like this, it gives me the Atlantic and Metro but I do not know enough to get the wild card.
i think I will need one more loop to do the wild card..
I want to try just the wild card as a loop,
Dim wild As Integer
wild = 9
For Z = 0 To 4 Step 4
y = 0
For x = 1 To 4
If x = 3 Then '3 is division
y = 1
ElseIf x = 4 Then
y = 4
End If
Do
Sheets("Sheet1").Cells(lastrowsheetone + wild + Z, x + 1) = Sheets("Temp").Cells(findmatch + wild + 1 + Z, x + y) '3rd
wild = wild + 1
Loop Until wild = 16
Next
Next
It outputs--------------------------------------------------------
--------------------------------------Wild Card 1 2 3 4 5 Ottawa Carolina NY Islanders Florida Buffalo Central x - St. Louis x - Colorado x - Chicago Pacific x - Anaheim x - San Jose Los Angeles Wild Card Minnesota Phoenix Dallas Vancouver Nashville Winnipeg Calgary Edmonton
then overflow error.. I'm more or less just guessing at the moment.Comment -
oilcountry99SBR Wise Guy
- 08-29-10
- 707
#150
Where you show 'stringtosearchfor' do I replace that with my variable 'nextday'?
This seems like such a basic idea 'find this and once it is found delete the that row and the rows below. Is this not something people typically do.
Sorry but I'm quite new to this, appreciate all your help.
So frustrating at times, after a lot of searching and experimentation with no success.... ArghhComment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#151I ended up getting the wild card with the following which is very basic..
Dim i As Integer
i = 9
Do
Sheets("Sheet1").Cells(lastrowsheetone + i, 2) = Sheets("Temp").Cells(findmatch + i + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + i, 3) = Sheets("Temp").Cells(findmatch + i + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + i, 4) = Sheets("Temp").Cells(findmatch + i + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + i, 5) = Sheets("Temp").Cells(findmatch + i + 1, 8)
i = i + 1
Loop Until i = 21Comment -
a4u2fearSBR Hall of Famer
- 01-29-10
- 8147
#152How do I do the delete portion after the string is found?
Where you show 'stringtosearchfor' do I replace that with my variable 'nextday'?
This seems like such a basic idea 'find this and once it is found delete the that row and the rows below. Is this not something people typically do.
Sorry but I'm quite new to this, appreciate all your help.
So frustrating at times, after a lot of searching and experimentation with no success.... Arghh
Sheets("Games").Rows(matched & ":" & lastrow).Delete
where matched=Application.Match(stringtosearch for, range,0)
and lastrow is the last row of your data you want deleted, the exact code for last row is in one of the earlier pages. It finds the last row that is populated, or you could set it to an arbitrary number that you know the data is within, i.e. 1000 if the data is in the first 1000 rowsComment -
a4u2fearSBR Hall of Famer
- 01-29-10
- 8147
#153I ended up getting the wild card with the following which is very basic..
Dim i As Integer
i = 9
Do
Sheets("Sheet1").Cells(lastrowsheetone + i, 2) = Sheets("Temp").Cells(findmatch + i + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + i, 3) = Sheets("Temp").Cells(findmatch + i + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + i, 4) = Sheets("Temp").Cells(findmatch + i + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + i, 5) = Sheets("Temp").Cells(findmatch + i + 1, 8)
i = i + 1
Loop Until i = 21Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#154I was able to do the same as an alternate to what you did plus the wild card,
Dim i As Integer
i = 1
Do
If i = 4 Then
i = 5
ElseIf i = 8 Then
i = 9
ElseIf i = 12 Then
i = 13
Else
Sheets("Sheet1").Cells(lastrowsheetone + i, 2) = Sheets("Temp").Cells(findmatch + i + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + i, 3) = Sheets("Temp").Cells(findmatch + i + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + i, 4) = Sheets("Temp").Cells(findmatch + i + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + i, 5) = Sheets("Temp").Cells(findmatch + i + 1, 8)
i = i + 1
End If
Loop Until i = 21Comment -
oilcountry99SBR Wise Guy
- 08-29-10
- 707
#155string to search for is your date you are looking for yes
Sheets("Games").Rows(matched & ":" & lastrow).Delete
where matched=Application.Match(stringtosearch for, range,0)
and lastrow is the last row of your data you want deleted, the exact code for last row is in one of the earlier pages. It finds the last row that is populated, or you could set it to an arbitrary number that you know the data is within, i.e. 1000 if the data is in the first 1000 rows
Here it is !!!!!!!!!!!!!!!!!!!!!! So rewarding when it finally goes as it should, thanks a million
Sub Schedule()
'
' Schedule Macro
'
Dim nextday As Date
Dim lastrow As Long
Dim findmatch As String
'Get schedule from cbssports
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.cbssports.com/mlb/schedules/day/0401/regular", Destination:= _
Range("$A$1"))
.Name = "regular"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'find last row on sheet
lastrow = ActiveSheet.UsedRange.Rows.Count
'Define tomorrows date
nextday = Format(Date + 1, "d-mmm-yy")
'Find tomorrows date
findmatch = Application.Match(CLng(nextday), Range("A1:A100"), 0)
'Clear rows below tomorrow's date (nextday)
ActiveSheet.Rows(findmatch & ":" & lastrow).Delete
End SubComment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#156Good work OIL!Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#157a4u2fear,
What's next for us?Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#158What about bringing in a table without using a temp sheet. This table will be also formatted as is from the site including colours and fonts.Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#159Here is a code to bring in MLB standings
Im not sure how to add come colors to highlight the titles
Sub Webquery()
Dim url As String
url = "URL;http://espn.go.com/mlb/standings"
With Worksheets("Sheet1").QueryTables.Add(Con nection:=url, Destination:=Worksheets("Sheet1").Range( "A1"))
.Name = "table"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = "2"
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End SubComment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#160'Change colors
Range("A1:l1,a20:l20").Interior.Color = RGB(25, 25, 112)
Range("A1:l1,a20:l20").Font.Color = vbWhite
Range("A2:l2,a8:l8,a14:l14,a21:l21,a27:l 27,a33:l33").Interior.Color = RGB(100, 149, 237)
'Add Border
Range("A1:l38").Borders.LineStyle = xlContinuousComment -
solringSBR High Roller
- 11-04-09
- 171
#161Any examples on importing and manipulating xml using vba?Comment -
pringlesSBR Rookie
- 11-26-12
- 41
#162
I received an answer directing me to
Blogger is a blog publishing tool from Google for easily sharing your thoughts with the world. Blogger makes it simple to post text, photos and video onto your personal or team blog.
What I want to do is just to get the XML feed to excel sheet, but this time i have to use VB which is why Im asking for your help as you know your way in VBComment -
oilcountry99SBR Wise Guy
- 08-29-10
- 707
#163Here it is !!!!!!!!!!!!!!!!!!!!!! So rewarding when it finally goes as it should, thanks a million
Sub Schedule()
'
' Schedule Macro
'
Dim nextday As Date
Dim lastrow As Long
Dim findmatch As String
'Get schedule from cbssports
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.cbssports.com/mlb/schedules/day/0401/regular", Destination:= _
Range("$A$1"))
.Name = "regular"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'find last row on sheet
lastrow = ActiveSheet.UsedRange.Rows.Count
'Define tomorrows date
nextday = Format(Date + 1, "d-mmm-yy")
'Find tomorrows date
findmatch = Application.Match(CLng(nextday), Range("A1:A100"), 0)
'Clear rows below tomorrow's date (nextday)
ActiveSheet.Rows(findmatch & ":" & lastrow).Delete
End Sub
For some reason I'm getting a type mismatch error 13 on the red code above, any ideas why? (It was working before)
thanks for your help.Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#164
I'm currently using the following to bring in my NHL standings. Problem I have is that I have formulas in R1:T31 and it loads my table to A31.
I would like to force it to start at A1.
Public Sub PullData()
Dim lastrowsheetone As Integer
Dim findmatch As Integer
lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
findmatch = Application.Match("DIV", Sheets("Sheet2").Range("C1:C300"), 0)
For x = 1 To 31 '30 teams
For Y = 1 To 17 '17 Titles
Sheets("Sheet1").Cells(lastrowsheetone + x - 1, Y) = Sheets("Sheet2").Cells(findmatch + x - 1, Y) 'this is Everything
Next
Next
End SubComment -
solringSBR High Roller
- 11-04-09
- 171
#165
Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#166Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#167Also I have a cell with 1:05 PM ET
How can i search from n1:n100 , when it finds a cell with time , I would like to
Change colors for every time it finds time
Range("a11").Interior.Color = RGB(25, 25, 112)
Range("a11").Font.Color = vbWhite
I tried something like this
Set FoundCell =Range("n1:n100").Find(what:=Time)
but no luckComment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#168Maybe it would be easier to say something like
if a1 contains "at" then
Range("a1: p1").Interior.Color = RGB(25, 25, 112)
Range("a1: p1").Font.Color = vbWhite
or would i need to use a $a$1?
the keyword "at" could appear until a100Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#169I have this
Dim cell As Object
For Each cell In ActiveSheet.UsedRange
If cell.Value = "STARTERS" Then
'cell.Interior.ColorIndex = 36
Range("A1: p1").Interior.Color = RGB(25, 25, 112)
Range("A1: p1").Font.Color = vbWhite
End If
But it only puts does the color in a1: p1
where I would like the color to be up 2 rows then fill acrossComment -
solringSBR High Roller
- 11-04-09
- 171
#170Both rows and columns are represented in the .Cells function by number. So, "A1" = row 1, column 1. "Z43" = row 43, column 26.Comment -
oilcountry99SBR Wise Guy
- 08-29-10
- 707
#171B_rad
Not exactly sure what your trying to do but this might be another option. Create a sheet formatted how you like as far as colours fonts etc. import your data/web query to a temp sheet and then copy and paste it to your formatted pretty sheet. Just make sure your imported data is in the same cells as your formatted sheet. They must coordinate.
Also when you paste use pastespecial valuesComment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#172Im just trying to add some colours to the table but only when there are certain key words.
Your way does work oil but, if there are more games then others it won't have the proper colours, if I add the colours to too many rows there may not be data.Comment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#173It would be nice if there is an option to import the colours from the site with the tableComment -
b_rad_1983SBR High Roller
- 01-07-13
- 127
#174Got it Oil...
This will search row a1 through a100. Look for the word STARTERS. Once found it will move up 2 rows and color the entire row as well as change the font color.
Dim r As Range
Dim i As Long
Set r = Range("A1:A100")
For i = r.Rows.Count To 1 Step -1
With r.Cells(i, 1)
If .Value = "STARTERS" Then
Range("A" & i - 2).EntireRow.Interior.Color = RGB(25, 25, 112)
Range("A" & i - 2).EntireRow.Font.Color = vbWhite
End If
End With
Next IComment
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