Hello,
Although I have lurked on this forum for some time now, this is my first technical post to the group asking for programming advice.
I have some knowledge of VBA, learning it as I go, and some of the advanced statistical functions of Excel.
Some of the posts and replies of this group have gotten me this far as a result.
I have written a program to analyze the winning Powerball Lottery numbers. The first five groups of numbers use 1-59 whereas the Powerball number is 1-35. I am using the statistical function frequency to analyze the numbers and provide me the number of times a certain number has been "pulled" for the winning number.
My question is, I am doing a data scrape using a VBA macro (called Draw) of a website. I paste this into a worksheet called WebScrape (Sheet 1) which contains this raw data.
I would like to add a button to CHECK RESULTS which updates the data from the webpage. The data results come in as one string and then I use a macro (called Parse) to parse the data into a series of two digit numbers.
VBA Code looks like this:
OK, now the WebScrape page looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Draw Date[/TD]
[TD]
[TD]Details[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD]7/11/2012[/TD]
[TD]05-22-36-49-55 23[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]29[/TD]
[TD]39[/TD]
[TD]59[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]7/7/2012[/TD]
[TD]03-05-29-39-59 29[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]19[/TD]
[TD]35[/TD]
[TD]39[/TD]
[TD]56[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]7/4/2012[/TD]
[TD]14-19-35-39-56 33[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]41[/TD]
[TD]44[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]6/30/2012[/TD]
[TD]07-15-20-41-44 22[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]34[/TD]
[TD]40[/TD]
[TD]46[/TD]
[TD]58[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6/27/2012[/TD]
[TD]06-34-40-46-58 06[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]41[/TD]
[TD]44[/TD]
[TD]53[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]6/23/2012[/TD]
[TD]01-03-41-44-53 30[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]17[/TD]
[TD]29[/TD]
[TD]56[/TD]
[TD]57[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]6/20/2012[/TD]
[TD]11-17-29-56-57 14[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]27[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]
The numbers to the right of the details are the parsed numbers. Can I do this automatically when I data scrape the webpage?
Then I have a worksheet (called Data) with the date and the winning numbers, with the Powerball (PB) number at the end. A third worksheet (called Calculations) analyzes the data table using the FREQUENCY function. On this page, I analyze the winning numbers (1-59) with a BIN (1-59) and then the Frequency (FREQ) using the following formula:
=FREQUENCY(Data!$C$2:$G$43,$B$2:$B$60)
I do a similar PB BIN (1-35) and PB FREQ for the Poweball (PB) numbers. The array works well and produces values. Then I have a column for the SORT BIN and SORT FREQ and SORT PB BIN and SORT PB FREQ. I copy and then paste the array values then sort according to FREQ, highest to lowest values.
My question here is can I append to this array, making it grow as I hit the button CHECK RESULTS, pulling the latest lottery numbers from the webpage and transferring them into the
Data worksheet? I would like to add to the array and let it grow larger as it adds more dates and numbers to the Data worksheet. Then the array would automatically update as more data is added.
I am sorry for the long post and complicated description but I cannot add my Excel file here as HTML Makeer apparently is no longer available. I also cannot post attachments.
Thank you in advance for your help on this problem.
MagicBill
Although I have lurked on this forum for some time now, this is my first technical post to the group asking for programming advice.
I have some knowledge of VBA, learning it as I go, and some of the advanced statistical functions of Excel.
Some of the posts and replies of this group have gotten me this far as a result.
I have written a program to analyze the winning Powerball Lottery numbers. The first five groups of numbers use 1-59 whereas the Powerball number is 1-35. I am using the statistical function frequency to analyze the numbers and provide me the number of times a certain number has been "pulled" for the winning number.
My question is, I am doing a data scrape using a VBA macro (called Draw) of a website. I paste this into a worksheet called WebScrape (Sheet 1) which contains this raw data.
I would like to add a button to CHECK RESULTS which updates the data from the webpage. The data results come in as one string and then I use a macro (called Parse) to parse the data into a series of two digit numbers.
VBA Code looks like this:
HTML:
Sub Draw()
'
' Draw Macro
' Scrape data from website
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.tnlottery.com/winningnumbers/default.aspx#pwrball", _
Destination:=Range("$A$1"))
.Name = "default.aspx#pwrball_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """dgPowerBallWinners"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Sub Parse()
'
' Parse Macro
' Parse data
'
' Keyboard Shortcut: Ctrl+t
'
Application.Goto Reference:="R2C3"
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=ActiveCell.Offset(0, 3).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _
(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
End SubOK, now the WebScrape page looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Draw Date[/TD]
[TD]
Draw Result
[/TD]
[TD]Details[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD]7/11/2012[/TD]
[TD]05-22-36-49-55 23[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]29[/TD]
[TD]39[/TD]
[TD]59[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]7/7/2012[/TD]
[TD]03-05-29-39-59 29[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]19[/TD]
[TD]35[/TD]
[TD]39[/TD]
[TD]56[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]7/4/2012[/TD]
[TD]14-19-35-39-56 33[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]41[/TD]
[TD]44[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]6/30/2012[/TD]
[TD]07-15-20-41-44 22[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]34[/TD]
[TD]40[/TD]
[TD]46[/TD]
[TD]58[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6/27/2012[/TD]
[TD]06-34-40-46-58 06[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]41[/TD]
[TD]44[/TD]
[TD]53[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]6/23/2012[/TD]
[TD]01-03-41-44-53 30[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]17[/TD]
[TD]29[/TD]
[TD]56[/TD]
[TD]57[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]6/20/2012[/TD]
[TD]11-17-29-56-57 14[/TD]
[TD]details[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]27[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]
The numbers to the right of the details are the parsed numbers. Can I do this automatically when I data scrape the webpage?
Then I have a worksheet (called Data) with the date and the winning numbers, with the Powerball (PB) number at the end. A third worksheet (called Calculations) analyzes the data table using the FREQUENCY function. On this page, I analyze the winning numbers (1-59) with a BIN (1-59) and then the Frequency (FREQ) using the following formula:
=FREQUENCY(Data!$C$2:$G$43,$B$2:$B$60)
I do a similar PB BIN (1-35) and PB FREQ for the Poweball (PB) numbers. The array works well and produces values. Then I have a column for the SORT BIN and SORT FREQ and SORT PB BIN and SORT PB FREQ. I copy and then paste the array values then sort according to FREQ, highest to lowest values.
My question here is can I append to this array, making it grow as I hit the button CHECK RESULTS, pulling the latest lottery numbers from the webpage and transferring them into the
Data worksheet? I would like to add to the array and let it grow larger as it adds more dates and numbers to the Data worksheet. Then the array would automatically update as more data is added.
I am sorry for the long post and complicated description but I cannot add my Excel file here as HTML Makeer apparently is no longer available. I also cannot post attachments.
Thank you in advance for your help on this problem.
MagicBill
