|
Excel Development Course
I get a lot of people asking for help with excel on here and a lot of the time it is the same things asked.
I have decided to run a crash course in excel for those that are interested and will be concentrating on things needed for racing in general. Some of the topics i will cover are. *The most common formulas i use. *Dealing with web queries *A simple data base I will be using Excel 2010 as my platform but a lot of what i show can easily be added to earlier version of excel. I am not a professional when it comes to teaching and my excel is limited so i am hoping what i have to offer will provide those in need a little more help. I am not going to create different threads for each topic it will all be in the one for easier reading and searching. If you are interested in learning let me know the types of things you would like to learn so i know what i need to cover. |
Hi Shaun, count me in, my main area of interest would be to do with Web Queries & also interested in any advice you have to offer about setting up a database.
|
Not a lot of reply's but am sure that is where most people could benefit from excel.
The type of data base i can do will be like a phone directory type where you would put in say a horses name then record specific info such as the price you backed it at or any info then when you need to go back and check that horse at a later date you just select it from your list and the info will be displayed. I will start on web queries and will post some examples and info to get started a bit later tonight. |
1 Attachment(s)
Yep,
Thanks My particular interest would be these types of pages http://tatts.com/pagedata/racing/2012/1/31/VR1.xml and how to code to extract precisely what is required and where to place it in a worksheet If you don't mind, hope this worksheet I got from another post here I been working on. It has a list of TODO's, hope these can be basic lessons. Thanks |
Sites like that are good if the have xml page to gather info from, but if they don't then web queries will do the same job if not better.
|
THE BASICS
The best way to learn web queries is to just jump right in and try them out. First thing is to start the macro recorder, this is the best way to go when you are first learning macros as it will record anything you do in excel and record the format needed for visual basic the language the macro is writen in. Create a new excel file then open it up, look at the very botton left next to where it says "Ready" you will see a button this starts the macro recorder. Put in a name for the macro and then hit ok, go to the tab labeled Data at the top of excel then find "From Web". In the address bar paste this address in http://tatts.com/racing/2012/2/1/MR/7 then press "Options" on the right, take note of the options on this page, we don't need to change anything here now so just click "Ok" then press "Go" on the next page. After the page loads you will see yellow arrows these indicate the different tables on the page, for the first test we will use the whole page, this is the arrow in the upper left corner, select it and it will change to green,once selected press import you then need to select the location you want the import to start at, in this case =$A$1 should be shown, then press the properties button on the next page go to the botton and check mark "Overwrite existing cells with new data, clear unused cells" then press "Ok" then press "ok" again. Once the page has been imported then press the record macro button again this will stop recording. Right click Sheet1 and select "View Code" then double click "Modules" then double click "Module1" and your macro will be shown on the screen, start by clearing the green text as it is not needed as this is just comment text. You should now see this code. Code:
Now lets look at what we have, the first part is telling us where it is connected and the destination of where to place the info With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1")) The rest of the information is what you would find on the options tab i showed you earlier and the properties tab, if you create another web query on another sheet and compare what is on those pages you can see where they are on here. Most of this information i don't change. Some pages you can get a specific table others you can't so it is trial and error some times. Go to sheet2 and Follow the steps from starting the macro recorder and put this url in http://www.tab.com.au/Racing/Inform...w&RaceNumber=01 When you get to the webpage i want you to scroll down until you see the arrow just above the race prices, when you highlight it a box will go around the prices, this is what we want so click it. Then continue on with the rest of the steps until you stop the macro recorder. Now see we just have the prices, now go in to view code and look at module2 you should have this. Code:
Scroll down to .WebSelectionType = xlSpecifiedTables see how this is different from the first one. If you look you will also see a new line has been added .WebTables = """BetGrid_DGTableOne2""" this is the name of the table we selected, different websites use different names for this, some sites use numbers. If you want to try different races for these websites you need to changed these parts in red. http://tatts.com/racing/2012/2/1/MR/7 http://www.tab.com.au/Racing/Inform...=1&MeetingCode=M&RacingCode=R&FromDate=2012-02-01T00:00:00&DisplayType=FinalDividendView&RaceNumber=01 The NSW/VIC tab urls change before and after the race so you can just use the url that is shown on the page you want to import. To rerun the macros again for a new page just go to "view" tab at the top of excel then "Macros" and double click the macro you want to run. That's it for now, try this on a few different sites you are trying to get data from and see what you can import. |
1 Attachment(s)
Today's race list in time order.
self explanatory, but there are 3 choices, simply copy and paste to workbook if you have downloaded it from this thread. |
Nice idea Shaun. I'm lurking with interest.
|
THE BASICS PART 2
Now you have the basic idea of importing webpages let’s make it easier to change races or pages. With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1")) This is the part of the code that tell the macro what url you want and where to place it, as you know some parts of a url change to load different pages so what we need to do is link that part of the url with a cell on the sheet so when you put in a different race number for example it will change the macro without you going in to the macro itself. As you can see there are ” at the start and finish of the url, these are important because when you add a cell location you will cut the url in half and these need to be added to any part of the url to make it work. When we add anything to the url we use the & sign this tells us the combine “this part&the next part” to make one part, I will show you an example of adding a cell reference to change date, race venue and number. Add a new sheet to excel; I will assume this is sheet4 for this example, right click and go to “view code” you can add “Sub Test4()” macro to an existing module. I am going to use the old Qtab site as they have not added as much junk to it as the new one. For proper formatting you should restrict VBA code to a reasonable length across the page, if code becomes too long and you want to go to the next line put this symbol in first_ then hit enter this tells the code to continue on the next line. The coloured text corresponds to the code needed in the macro. Here is the original link www.tabonline.com.au/2012/02/02/NR01.html The red is the standard address that does not change. The green is the date the code formats the date the correct way for the site. The blue is the venue. The orange is the race number. The yellowgreen is the last part of the url. On the new in cell A1 you put the date, in cell B1 you put the venue in cell C1 you put the race number in single digit, then add this code to D1 =IF(C1<10,0&C1,C1) The code adds a zero to races below 10 Code:
If you can get this to work see if you can get the NSW/Vic page to load in the same way. |
Quote:
I suppose in Excel version 2003 , there aint no button down there? |
If you look under tools then macro you will find the recorder there for 2003.
One thing i forgot to mention with excel 2007 onward is when you go to save the file the first time you need to change the file type to "Excel Macro Enabled Workbook" Once that is done you can save it each time you close it. |
Done BASIC 1
Did not do THE BASICS PART 2 cos I already do that with cell refs. good show, Thanks |
race lists 20120204
1 Attachment(s)
The tatts url to get a racelist is
in cell A1 2012/02/04/raceday With Selection.QueryTable .Connection = "URL;http://tatts.com/racing/" & Range("A1") & ".html" Then there is a series of macro to get it to go to columns For some reason it won't work when I try . & Range("A1") & ".xml" |
ROBOT
Shaun.Thanks for your basics training.Can manage all up to modules Where do I find them.If double click modules in the drop down it goes to module 2.What am I doing wrong.Have got the page for MR1 up but all I get is sheet1(code) Would like to continue to learn more as discussed before Maybe a slow learner.
Tom the ROBOT |
No macros on that sheet, also you don't need it in a zip file you can just upload the file, but i am sure the Zip file reduces the size if you are using an older excel.
For those that wish to use a new version of excel you can get a trial copy for free that will last you 6 months, at the end of 6 months you have 3 choices. 1) Continue to use the program and get a popup when opening saying it is unregistered and not be able to update but all functionality is the same. 2) Format your hard drive and re install the program and have it again for 6 months. 3) Purchase the program. I use option 2 but if i am lazy option 1 is sometimes in effect, has been for the last 2 years. |
1 Attachment(s)
This is what you should see when you open the VBA editor, you may have 2 or more modules depending on how many macros you created.
|
Someone needs to teach me how to post images that you can see but are not to large to upload.
|
HI Puntz,
I've been caught out in this same issue before and the Unitab XML feeds can be a little misleading in this respect. It may relate to the format of the date you are using in cell A1. I notice you have it in YYYY/MM/DD format, but I've found that when it is a single digit day and single digit month (i.e. 2012/01/04), Unitab's xml feed can't interpret it. It needs to be changed to 2012/1/4 To reformat cell A1, right-click on it, select Format Cells, then Numbers tab, then Custom from the Formattingh Options. In the 'Type:' field, enter 'YYYY/M/D' and it should now display the date as 2012/1/4, instead of 2012/01/04. Don't worry, it will still interpret 2012/10/10 correctly. Hope this helps. Neurokahuna |
1 Attachment(s)
Thanks Neurokahuna
I'll work on your suggestions later and do some tests. Have uploaded the current date method. The point is eliminating as much typing as possible so no typo errors happen. I hope the right format will happen on this same worksheet. By all means correct it if so to make it work like it ought to, show and tell is appreciated. Puntz |
Quote:
Open MS Paint Print Screen and Paste to Paint Save Close Then upload here, usually works. |
Yeah i tried that but file was to big so i cropped it, guess i made it to small.
Trial version Office 2010 i can explain at a later date how to extend for 6 months free use. http://technet.microsoft.com/en-us/evalcenter/ee390818 650MB download so depending on your connection speed my take some time. |
Thanks for your posts guys, been swamped but hope to have a look at it all next week.
|
Anyone got an idea what is wrong with the following code? Cell A1 contains the date, E1 the 1st letter of the race code eg "M', D1 the race number in 2 digit format eg '01', - as nothing happens when I run it.
Code:
|
I had a look at your code and found a few errors but after i fixed them i still couldn't get it to work.
Sometimes this happens and because i don't know heaps about macros i came up with another way of doing this. What i do is create the url i need on the sheet and add the variables there, the good thing about doing this is you can see the results and check it with the original to find problems. So here is what i have Adjust the sheet to match where you want the info. A1 =Today() this will put today's date or you could just put a date. C1 you put the race number in single digit form D1 =IF(C1<10,0&C1,C1) E1 you put the letter of the meeting such as M F1 ="http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1&MeetingCode="&E1&"T&RacingCode=R&FromDate="&TEXT(A1,"yyyy-mm-dd")&"T00:00:00&BetType=Win&RaceNumber="&D1 here is the code you need to put in the VBA sheet Code:
|
To remove the actual hyperlink right click the cell and select " Remove Hyperlinks"
If you don't want to see the url just reformat the text in that cell to the colour whte or what ever the background colour is. |
Thanks Shaun, I made a few adjustments mainly so I can use it on any worksheet & also adjusted the url to -
="http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1&MeetingCode="&E1&"&RacingCode=R&FromDate="&TEXT(A1,"yyyy-mm-dd")&"T00%3a00%3a00&RaceNumber="&D1 Code:
|
THE BASICS PART 3
Now we have a couple of ways to get info in to the sheet we need to make it more efficient, this will also speed up the time it takes to import. We need to add a couple new macros to the sheet, these macro should be added to the top of the sheet before the inport macros or any other macros you have added. Code:
Code:
The first code uses the MSXML2.XMLHTTP procedure to import the info, this is what I understand it does but I may be wrong. The second code downloads your info to a temp file. You need to make changes to your web query to use these 2 codes, if we use the above code to import the Vic tab odds these are the changes we need to make. Sheet1 has your variable info Sheet2 has your imports Code:
I would be a good time to add a button to sheet1 that runs the macro, if you want this all on the one sheet that's fine just make sure you adjust the the first section that clears the sheet to start at say A3 and also adjust the Destination to A3 and also change sheet2 to sheet1 |
norisk - that's fine, once you have the idea and it works you can make a lot of different changes to suit the site you want to use it on and the info you need.
I have posted many sheets on here with different things i have added but with this basic steps most people should be able to get web query's up and running. |
Good stuff Shaun, the speed difference is impressive even with such a small query.
|
The one main issue with doing this way is you lose the ability to refresh unless you run the macro again, I have not had a lot to do with refreshing a macro as I have not needed it so far but here is an option for auto refresh.
Add this code to the bottom of the module. Code:
Then you need to create 2 buttons to click so do this, select excel sheet where you have the race options , go to the top panel look for “Developer” if developer is not shown go to “File”/“Options”/”Customize Ribbon” and make sure it is checked on the right hand panel. Select “Developer” then “Insert” then select “Command Button” in ActiveX Controls. Paste the button on the page then double click it then paste this between the top and botton line. Code:
Repeat instructions to create another button and double click it and paste this in. Code:
You can then rename the buttons “StartButton” and “StopButton” you then need to activate them by pressing design mode to turn it off. You can adjust the refresh time in “RepeatMacro” just press the buttons to start and stop refresh, you need to stop refresh before changing the race when you hit start it will switch races. |
Shaun, I am partially lost at this point,(THE BASICS PART 3) but impressed at the speed of the imports to say the least using the XML thing.
The way I do my "refresh" is race by race on the race start time order list. By "refresh" I mean a Loop in starting at the top using the Do until ----------------------- So it's ( your code ) Quote:
and the rest of your code at this point. ---------------- FOR TESTING, I have used a counter method, so when it reaches "5", it simply gets the next race on the list. This will be deleted later on. Quote:
How is the code written to get RaceDisplayStatus to appear in Sheet 1 Cell J2 please? And the results if possible for that race. Thanks. Puntz |
So what you want it to do on the unitab site is select a race then keep refreshing then when the race is over you want it to move to the next race?
|
That is for the new site correct.
|
Yes, that is for the tatts site cos that is the one I am working on now, but later I'll move on to the other sites.
For now, the Do Until requires the status, whatever it is, if it's, a "Time", "PAYING","INTERIM","CLOSED", "ABANDONED" "PHOTO" "PROTEST" etc etc and/or whatever else the scenario may be. Eventually it will get to "PAYING", which will be the official RESULTS. The actual RESULTS payout figures may be best go to Sheet2. During some testings I don't wait around for PAYING, and choose INTERIM then move on to the next race. The time limit is important to. If lets say a race has a advertised start time of 3:00pm and it's Looping, there has to be a limit or it will Loop forever, so therefore I implement a Variable factor of let's say 20 minutes after advertised race start time LIMIT. So if at 3:20pm there is no status or they are still negotiating a PROTEST, then 20 minutes past 3:00pm ( Variable), Next Race..., Puntz |
What do you use to get the race times in order and where do you get them?
I was having a look at your last sheet the screen updating is doing twice the work, have it set like this Application.ScreenUpdating = False Rest of macro here Application.ScreenUpdating = True What that does is stops the screen updating so it just looks like the info appears and you don't see the macro working, if you do the same for the clean up macro will look smother.all the |
Ok, I did not include the entire macro, only the section.
----------------------------- Quote:
It's in another workbook using the old html code Quote:
Cell A1 is: 2012/02/06/raceday This brings up the entire race card ---------------------------------- Quote:
Yes, I am still tweaking as I go along. I'll post the new day's race list tomorrow at around just past 9.AM. I need the RESULTS though or the project/workbook cannot be completed, and won't be able to get past BASIC 3. Thanks Puntz |
1 Attachment(s)
Today's Race list
There are 3 sheets 1: with only horse and trots 2: horse-trots-greys 3: all races on card the all races on card sheet, the overseas racetimes may show past midnight so effectively it is not in true time order. The code I use to delete a race row goes something like: add a sheet and call it: DAYSRACELIST USE THE ONE POSTED HERE AS A BACK-UP, COPY AND PASTE FROM BACKUP TO DAYSRACELIST, THEN YOU NEVER LOSE THE ORIGINAL. Quote:
Puntz |
Shaun,
On the Workbook you have here where it gets the prices using xml how is it coded to gather other information ? For example Length,Distance etc etc This xml makes no sense, it's not like the code when Record Macro is written where it understandable-linear--> xml looks like "2-way speak" Set runnerNumber = runner.Attributes.getNamedItem("RunnerNo") If Not runnerNumber Is Nothing Then Sheet1.Cells(i + 5, 1) = runnerNumber.Text Ya know? It's not like, "put race length in Cell B1" Give this some thought, since when does a punter need to learn to program a PC to win a race when the punter already knows more than the programmer about racing and betting ? But if your answer is something like, "well what goes round comes round, it is the age of the programmers" I say ****, Pitsburgh Phil never used a computer! http://en.wikipedia.org/wiki/George_E._Smith_(gambler) Quote:
|
xml is different from what i have been doing in this short course, xml is the website designers way of providing all the information that a person needs to put that in excel.
The VBA that i have posted is excels way of gathering information from the website that is why not all information is able to be separated in to individual bits before download. This is why some times you can download individual tables and other times you have to get the whole page and take what you need. I am not good with xml i can only understand some from there example so i am not the best person to ask about this. As for your comments about computers helping punters win, well to be honest i think it is the punter helping the computer win. Computers are no smarter than what information they are given, they can calculate things and display other things in a layout that is easier to understand. The speed they can accomplish these things can be very fast. |
comment are just my way of breaking monotony, I have a soft spot for programmers, I surely do.
I went to TAFE actually to enter a 6 year IT course and failed the intro., ( I did not tell them my "secret" that I wanted to make my own horse race program) they said I need to go back to school and learn Trigonometry. I said OK. So I went back the TAB and looked for a horse with a similar sounding name. When I asked the woman at the counter after placing a bet, "Do you know Trigonometry?" She pointed to her wedding ring! Anyway, Quote:
Yes, that's what I been doing using another web query with html at the end, and yeah, like you say, "take what you need" Thanks |
| All times are GMT +10. The time now is 04:20 PM. |
Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.