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.

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

Attached Files

xml propunlessons_test ws.zip (32.2 KB, 1744 views)

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.



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.



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.

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.

Attached Files

DAILY RACE LIST.zip (26.4 KB, 1615 views)

Nice idea Shaun. I'm lurking with interest.

__________________
Pixie

"It's worth remembering that profit isn't profit until it's spent off the racecourse." -- Crash

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



If you can get this to work see if you can get the NSW/Vic page to load in the same way.

I suppose in Excel version 2003 , there aint no button down there?