• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

New To Excel - Turning Copied Data into Useful Data

N

naberben

New Member
Hello,


I am just getting my feet wet with Excel, I would truly appreciate any knowledge on this subject.


As a gambling sportsman, I am interested in copying internet data results from previous horse races and turning that data into useful win percentages and payouts.


So far I have completed 2 steps:


1) I receive all my data from Balmoral Park (http://www.balmoralpark.com/index.cfm):

For results by date: http://www.balmoralpark.com/EntriesResults.cfm?Cat=R

For initial starting data:

http://www.balmoralpark.com/ShowResults.cfm?Cat=R&Results_ID=2284


2) I select all (Ctrl+A) the data and then copy (Ctrl+C) the data. Then I open my Excel file and use a special paste to paste the data as text into Excel.


Here are my first problems:


I. When I paste the data, all fractions are converted to dates.


II. Likewise, all trifecta numbers (ie. 3-4-6) are converted to dates.


I need your help and guidance in:


A) Solutions to problems I and II mentioned above.


B) Create a macro that selects parts of the copied data and organizes it in another sheet named 'Overall 2011 Results' into columns ordered 'Track', 'Date', 'Race Number', 'First Place', 'Second Place', 'Third Place', 'Fourth Place', 'Win Price', 'Place Price', 'Show Price', 'Exacta Price', '$2 Trifecta Price', and '10 Cent Superfecta Price' from left to right, respectively.


C) Using the same marco from (B) on results from Balmoral Park of all 2011 dates to expand my data field to display in the 'Overall 2011 Results' sheet in the same workbook.


D) Compiling all 'Overall 2011 Results' data into a 'Percentage' sheet that displays the percentages of the winning horses and winning combinations of horses, along with what combinations paid the most.


Please note I have never designed a macro before, so any knowledge you can give me is useful knowledge. I appreciate all of your help and concern. Thank You!


References


"Balmoral Park Racetrack." Balmoral Park. Web. 06 Dec. 2011.

<http://www.balmoralpark.com/index.cfm>.
 
Hi ,


The solution to the first problem is straightforward. Excel ( I do not know which version you are using ) has a facility which can retrieve data from the Web.


Click on Data -> From Web ( Get External Data ) ; enter the website address into the address input box.


Click on Options , and check the Disable Date Recognition checkbox.


Click on Import , and specify the cell address where you want the data to be copied.


Narayan
 
Thanks Narayan,


I am using Microsoft Excel 2011. I had no idea you could retrieve data from the Web, thank you very much.


Although, when I enter http://www.balmoralpark.com/EntriesResults.cfm?Cat=R into the 'Address:' field I am unable to import 'Results' from December 3rd, 2011. For when I click on the 'Results' hyperlink, the webpage is brought up in an internet browser (either Internet Explorer or Google Chrome). Any suggestions?


Thank You,

Naberben


References


"Balmoral Park | Entries and Results." Web. 10 Dec. 2011. <http://www.balmoralpark.com/EntriesResults.cfm?Cat=R>.
 
Hi Naberben ,


Try carrying it through till the last page within your browser ( IE or Google Chrome ) , and then give that link within Excel. For the instance that you have mentioned , clicking on :


http://www.balmoralpark.com/EntriesResults.cfm?Cat=R


brings up an initial page. Clicking on the Results link for December 3 , brings up the results page whose link is :


http://www.balmoralpark.com/ShowResults.cfm?Cat=R&Results_ID=2284


Pasting this link within Excel , shows the same page within the Excel window , so that you can now select the table you wish to import.


Can you confirm whether this works ?


Narayan
 
WOW Thanks Narayan,


Great call on pasting the link directly into the "Address:" bar in the "New Web Query" window, it worked perfectly!


Now the next major task is creating a macro to sort through the data from the web and condensing it into columns of Date, Track, Race Number, and the corresponding first four horses of the race onto a new sheet. I will be using http://www.balmoralpark.com/ShowResults.cfm?Cat=R&Results_ID=2284 as my data, can you help me or point me in the right direction of learning some basic macro skills?


I appreciate all your help and I apologize for my delayed response.


Thank You,

Naberben


References


"Balmoral Park Racetrack." Balmoral Park. Web. 16 Dec. 2011. <http://www.balmoralpark.com/ShowResults.cfm?Cat=R>.
 
Hi Naberben ,


If you are the only person going to be using the worksheet , I think macros are not necessary ; if you can specify all the results that you want from the raw data , I think you can get what you want , using just formulae.


Can we take it one result at a time ? What is the first result that you want ?


Narayan
 
Thanks Narayan,


You have been super helpful throughout this process, that you for your time and effort!


Here is my Excel workbook:


https://rapidshare.com/files/51695271/Horse_Racing_Statistics.xlsx


Alright, here are my steps:


1. Open Blank Excel File -> 'Save As' 'Horse Racing Statistics'


2. Getting External Data From Web (with respect to Narayan 5 posts previous)


Click on 'Data' Tab -> Click 'From Web' icon in 'Get External Data' group


Opens 'New Web Query' window -> copy and paste

'http://www.balmoralpark.com/ShowResults.cfm?Cat=R&Results_ID=2284' into 'Address:'

bar -> Click 'Go' -> Click 'Options...' button


Opens 'Web Query Options' window -> check the 'Disable date recognition' box ->

Click 'OK'


In 'New Web Query' window -> click 'Import' button closing 'New Web Query' window


After downloading 'Import Data' window opens -> in 'Exixting worksheet' field enter

'=$A$1' -> click 'OK' button


Resulting in 'BALMORAL PARK RESULTS' data from 'Saturday, December 03, 2011'


3. Rename 'Sheet1' as 'BP 12-03-2011'


4. Click on 'Sheet2' and rename 'Cumulative Results'


5. On 'Cumulative Results' sheet 'Merge & Center' in the 'Alignment' group on the 'Home'

Tab cells 'A1:G1' and 'H1:V1' -> Type 'Results' and 'Pricing' underlined and in bold,

respectively


In cells 'A2:V1' type 'Date'; 'Track'; 'Race'; 'First'; 'Second'; 'Third'; 'Fourth';

'First Win'; 'First Place'; 'First Show'; 'Second Place'; 'Second Show'; 'Third

Show'; 'Win, Place, Show Pool'; 'Exacta'; 'Exacta Pool'; 'Double'; 'Double Pool'; '$2

Trifecta'; '$2 Trifecta Pool'; '10 Cent Superfecta'; and '10 Cent Superfecta Pool'


6. Making Data from Sheet 'BP 12-03-2011' appear in the respective columns of Sheet

'Cumulative Results'


--->> This is where I get stuck <<---


Currently, cell 'A3' of 'Cumulative Results' sheet has the formula '='BP 12-03-

2011'!A3' to display 'Saturday, December 03, 2011'. I used this same manual method

for the entire 'Row 3' of 'Cumulative Results'.


What would be the easiest and fastest method to automate the organization of all the

data from sheet 'BP 12-03-2011' into the 'Cumulative Results' sheet?


Please let me know if I am not clearly communicating my Excel thoughts and desires, for this is as much a communication exercise as a Microsoft Excel exercise.


Thank You,

Naberben


References


"Balmoral Park Racetrack." Balmoral Park. Web. 17 Dec. 2011. <http://www.balmoralpark.com/ShowResults.cfm?Cat=R>.


RapidShare – Secure Data Logistics. Web. 17 Dec. 2011. <http://rapidshare.com>.
 
Hi Naberben ,


Quite an interesting exercise this is turning out to be !


On going through the worksheet , the first point is that we are not in a position to use formulae to get all of the data into the Cumulative sheet.


I used the following formula in cell C3 to get the first three items viz. "FIRST RACE" , "SECOND RACE" and "THIRD RACE" :


=OFFSET('BP 12-03-2011'!$A$4,(ROW()-ROW($3:$3))*34,0)


However , thereafter the wrong items started appearing ; this is because of the varying number of horses in the different races. How do we tackle this ?


If all the races had 8 horses or 10 horses or any other fixed number of horses , this would have been far simpler. As it stands now , I think a VBA procedure is the way forward. What do you think ?


Narayan
 
Hi Narayan,


I completely agree with you. The variance of number of horses in each race, the pricing gimmicks of each race, and soon the variance of the number of races each day call for a VBA procedure.


Now the trouble is I have never worked with VBA procedures. I'm not far enough along in my Excel self-help book and I'm just scratching the surface of Chandoo.


Can you help walk me through your plans for a VBA procedure? Else if, can you point me to a credible source where I can learn how to create my own VBA procedure?


Thank You,

Naberben
 
Hi Naberben ,


Give me some time to think it through ; as far as I can see , the only variable seems to be the number of horses. I am trying to put together some kind of formula which will give us what we are looking for.


I am trying to find all the occurrences of "Horse" in column H ; this is the start of the list of horses ; once we get the starting cell , we need to find the last cell in the list , which will allow us to get the count of horses ; we can then use this to step through the data.


The first occurrence of "Horse" is simple :


=MATCH("Horse",H1:H1000,0)


I am assuming that the list cannot be bigger than a thousand rows. Assume this formula is in cell U1.


Thereafter , the subsequent occurrences of "Horse" are through :


=MATCH("Horse",OFFSET($H$1:$H$937,SUM($U$1:U1),0,937),0)


Now for the next step !


Narayan
 
Hi Naberben ,


I have uploaded a worksheet with some formulae ; can you check out the correctness of the data resulting from the formulae ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21122


The only remaining problems pertain to DOUBLE and 10 CENT SUPERFECTA , since the presence of DOUBLE is not mandatory ; when it is present , 10 CENT SUPERFECTA occurs after DOUBLE. I don't know how to consider this , using simple formulae , since now we need to check for whether DOUBLE is present or not.


Narayan
 
Thank You very much Narayan,


Your formulae look great, I just have to take time to play with it to understand it completely. Unfortunately, I will no longer have time to seriously research Excel via Chandoo.org for I must attend college this spring semester. I look forward to learning for you this summer.


Again, thank you for your knowledge astounds me.


Thank You,

Naberben
 
Hi Naberben ,


Thanks. All the best for your college term. I am sure your knowledge of Excel will be useful at college.


Narayan
 
You must log in or register to reply here.
Back
Top