Macros in Excel

[SPEEDHORSE]

I have an Excel spreadsheet data base for past performances. Can macros get for me the last 6 past performances/starts for a horse without resorting to copy paste? Can macros get for me the highest Speed Figure from a horse's last 6 starts? If not what would be the best way to use macros for my Excel spreadsheet? Any help would be highly appreciated...GOD BLESS !!!

[raybo]

I have an Excel spreadsheet data base for past performances. Can macros get for me the last 6 past performances/starts for a horse without resorting to copy paste? Can macros get for me the highest Speed Figure from a horse's last 6 starts? If not what would be the best way to use macros for my Excel spreadsheet? Any help would be highly appreciated...GOD BLESS !!!

I assume that somewhere in your spreadsheet you have an area where the horses' last 10 running lines are displayed. If so you could put a macro button in your spreadsheet. then record a macro to do all the copy/paste actions to get each horse's last 6 running lines and paste them to another area of the spreadsheet, then assign this macro to the macro button. Click the macro button and you now have each horse's last 6 running lines.

Then, after you have the last 6 running lines for each horse, write a formula to get each horse's highest speed figure.

Record another macro to copy/paste each horse's highest speed figure to yet another area of the spreadsheet, then copy this macro code and add it to the code for the first macro you recorded.

Then the original macro would first get the last 6 running lines for each horse and then proceed to run the 2nd macro to get each horse's highest speed figure from those last 6 running lines.

[sjk]

I used Excel macros many years ago to parse charts. That was long enough ago that I did not need to learn VBA since there was a separate language. I don't know how to code what I would do but I believe you can create a macro by performing the operations and letting the program do its own VBA.

What I would do is sort the data by name and date. Then use a new column to find those lines which represent the most recent start by a runner (the name in this row will be different from the name in the row above). In another column find the max of the speed rating of this row and each of the 5 below.

Hope something like that works for you.

[raybo]

Keep in mind that anything you can do manually with the keyboard or mouse can be recorded in a macro then that macro can be assigned to a macro button. Macros can be combined by copying each macro's code and pasting them into one "composite" macro that will run all the macros with the click of one macro button.

You do not have to know VBA to do this.

[raybo]

Are you using Infotran or some other parser to import the data into Excel or are you using Excel 2007 and just opening the data file in Excel?

If you're using a parser then just modify the control file to get only the last 6 running lines.

It would help to know more info about the layout of the data in your spreadsheet.

[SPEEDHORSE]

Thanks for your immediate response to my post, Bro Raybo. I just sent you a PM, but your inbox seems to be full.

[ranchwest]

Maybe it is just because I'm a programmer, but I find the thought of using a spreadsheet instead of a database to be scarey.

[startngate]

Maybe it is just because I'm a programmer, but I find the thought of using a spreadsheet instead of a database to be scarey.No doubt!

Even MS Access gives you a much easier way to 'slice and dice' data and it doesn't take a programmer to be able to use it.

[Donnie]

The "programming" behind this will be a little tricky as sometimes a horse may have 6 rows, sometimes 10, sometimes 1.....don't know if a macro has enough power to do this....it could be coded in VBA using a looping function, though.

[raybo]

Thanks for your immediate response to my post, Bro Raybo. I just sent you a PM, but your inbox seems to be full.

Ok, deleted some messages from my PMs. You can send your, message now.

[SPEEDHORSE]

Many thanks to all of you who responded to my post. I will be considering your suggestions.....GOD BLESS !!!!

[hcap]

I have an Excel spreadsheet data base for past performances. Can macros get for me the last 6 past performances/starts for a horse without resorting to copy paste? Can macros get for me the highest Speed Figure from a horse's last 6 starts? If not what would be the best way to use macros for my Excel spreadsheet? Any help would be highly appreciated...GOD BLESS !!! I suspect you need vba to deal with with this. VBA is the programming language built into excel. Combining vba and the built in "advanced filter" would be the way to go. The advanced filter allows you to pull out all running lines of a particular horse.

1-In another part of your excel sheet-other than where you store all running lines-you need to make a list, or table, of all horses in one race. You can direct the advanced filter thru' vba to do this.

2-You do need vba to "loop" thru' or "walk down" each member of the list one at a time, to extract all running lines of that particular horse contained in your main database. The filter will allow you to copy all these lines to another part of the sheet. The filter will also list these lines by date. Most recent at the top of the list, with previous dates in proper order.

3-As each horses' records are extracted, you do whatever calculations (finding the highest speed in the last 6 starts) you want one at a time, and copy those results to another location.

4-Using vba's database functions are substantially slower than the built-in advanced filter feature of excel. I always have found using vba to direct the "built-in" features of excel to be the way to go.

[hcap]

To learn about the advanced filter.

http://www.contextures.com/xladvfilter01.html



An example of vba code I use to manipulate the advanced filter.

Range("A20").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("A1:AI2"), CopyToRange:=Range("FK1:GN1"), Unique:=False

[SPEEDHORSE]

Hi everyone, I just want you all to know that this website is really a great place to get help from fellow ROUS (Registered Online Users).

When I posted if macros can be utilized in an Excel database file, to get the last six past performances for every entrants in a race card as well as to get the highest Speed Figure out of its last six starts. Several ROUs responded but the most patient and helpful of all was none other than a fellow who goes by the handle hcap.

This wonderful man who I fondly call Bro. Harry, was able to develop a program, using macros and VBA codes, that shows all the past performances for each entrants in a race card covering a period of two months. The reason for the two months worth of race result charts is for the purpose of selecting "good races" only, and to determine a horse's recent ability using its Speed Figures as a measuring stick.

The program provided a SELECT button that will test a horseplayer's paceline selection ability. After marking the paceline selected with an x, all pacelines selected will be transferred to a worksheet called PICKS. The horseplayer can also do some pace analysis before making his final selection, because the Excel database file includes Fr1 and EP fps velocity computations.

I have been a lurker on this site for the past six years, and it was only this year that I finally got the courage to register because I came from a third world country, called the PHILIPPINES.

I'm so glad that I got to know a great person in Bro. Harry, who was really accomodating and understanding in guiding me every step of the way till this program became a reality.

MAY THE LORD REPAY YOU TEN TIMES FOR YOUR KINDNESS, BRO HARRY....GOD BLESS !!!

[Tom]

I second that - hcap has been a huge help to me with some things in Excel.
Kudos are in order! :ThmbUp::ThmbUp::ThmbUp: