hypersoniq's Blog

Anyone have a good link or can describe the process of loading an array from an excel file? Do I need to use the Jet ODBC engine for this?

I'd rather not go through the cumbersome line-by-line, element-by-element process of using a .csv file to load the array... There must be some way of just grabbing the data straight from excel.

I'm going with

Dim intPick3(1 to 10376, 1 to 3) As Integer  '10,376 is # of draws in Pick3

for the array of PA evening pick3 data. If I recall correctly, this should give me 3 columns * 10,376 rows (31,128 elements). "10376" will likely be replaced by a variable so I can have it read the actual number of draws for re-runs and updates.

I did work with VB6 and Access, but I can't unearth any decent info on working with excel. All I want to do is use a loop to load the array from an excel file that has the 3 digits in the first 3 columns, not reading dates and the first subscript in the array will represent the draw number.

thanks in advance for any help :-) 

Interesting how different this new suite of systems picks numbers. Using the game's own randomness against itself, the most noticeable thing about each pick is how far off it is when it's wrong... I am only back 100 draws in pick3, 10,000 to go. I finally devised a method to automate the whole thing, but I have to re-install VB6 to put it into action... and the CD drive is broke. (may tinker with a network-based install with the functioning "newer" PC that the rest of the family uses.)

was working on Pick4 LRS excel file when we had a quick power loss... tried to re-open the file and it was corrupt :-(

thumb drive saves the day! only lost about 30 min. of work which was easy to re-do. My files are all massive for LRS/LoopRS, and they don't even put a dent in the storage space.

I gave my old one to my kid for school, so I had to use some of that $500 to trat myself to a new one (Sony microvault 4GB) normally $90 but staples had it on sale for $49. ALREADY saved the day...

If i can pull in another win on P3 I have my eye on this cool little handheld PalmTX that works with excel files using DataVIZ... hmm

Given the fluke win this week, I figure on dropping some of the cash on a live-fire LoopRS/LRS test for the month of may...

Powerball (one powerplay ticket per draw)

Cash5 (one per draw)

Pick3 ($1 st. pick per evening draw)

pick4 (one 50cent st. bet per evening draw)

total cost to run the test... $95.50

the fact that the process is continually being tweaked makes me think I should wait a bit, but I'm just too curious.

Criteria: $95.50 set aside to play, winnings for the month MUST exceed this amount (I have a tracking sheet in excel ready for this). Box hits are not being played so they don't count.

hits that make the test a success...

1 st. on pick3 or pick4

one hit on Cash5 for 4/5 MIGHT do it (hard to say with pari-mutuel prizes tho) or enough 3+0 hits (LoopRS picked two 3/5 hits in 2 weeks).

one 3+1 or 4+0 hit on PB definitely (because of the extra $9 figured in for power play), or some combination of 3+0/2+1/1+1/0+1 that exceeds $95.50

I will keep track of the wins on this particular thread as comments... hopefully I have something to post... either way, I never won the pick3 before so I guess 2-1-5 is my "lucky number" but I won'tbe playing that again unless LRS calls for it. IF the hit comes on P3/P4 I'll start posting those here (for entertainment purposes only) once the system is stable... I can generate the whole month of pick3 at once, but because the system is still being tweaked, they are subject to change frequently.

just for kicks, tonight's LRS numbers for PA eve...

Pick3#  0-6-3

pick4#  2-2-2-0

and for sunday PAeve

pick3#  8-7-3

pick4#  9-0-4-2

Given the fact that this is the first system I have worked on that uses the game's own randomness against itself, I am particularly excited about it... doesn't hurt that it won me $510.50 this week either:-)

Note the games that are NOT in the test

Pa pick3mid/pick4mid (don't want to reward state for computer drawings)

PaMatch6/Mix-n-Match (don't believe in $2 games unless I choose to "powerup" in PB)

Pa Treasure Hunt (no data/too "gimmicky")

IF the test is a success I will NOT have the time to do these workouts for other states but I could post the PA pick3/pick4 sheets to show how it was done. I WILL sometime this year work up sheets for South Carolina pick3/4/palmetto cash5 because win or lose we are planning to move there next summer...

Since this is my first multi-game live test and I like to name everything...

this is the "Synch-O de May-O" test :-) because I am using the SAME system to pick 4 different games. Live test not possible without the kindness of Lady Luck and the number 2-1-5, so I'm excited, even more excited that the whole thing is being funded by a win rather than my own money.

for the heck of it, I played a LoopRS Cash5 pick yesterday and it matched 3/5 for a pari-mutuel payout of $10.50

I decided to just grab the complete set of LoopRS/LRS pix for tonight and play them all (on PA's nickels)...

Cash5 1/5 (no joy$1 bet/$1 Loss)

Pick4eve , 2 numbers but only one in position (50 cent bet/50 cent loss)

pick3eve , 3 numbers IN POSITION!!! ($1 straight bet/$500 WIN)

WOOOOOOOOOOOOOOOHOOOOOOOOOOOOOOOOOO

profit for this week is $510.50 loss is so far $1.50 cost to play winners $2

net gain of $507 on a one-shot straight system, the first time I played it!

Looks like I won't be "retiring" from the spreadsheets after all!!!

BTW the LRS pick3eve for tomorrow calls for 0-8-8

and I'm still only playing ONE powerball ticket tomorrow...

Getting at once closer yet more frustrating with each PB draw...

analysis of the last draw...

PB LoopRS pick 07-35-36-44-47 + 32

Drawn Numbers 22-26-28-33-45 + 17

not so hot... PB number proving to be the most elusive so far.

from "paper play"... last night's PA cash5

LoopRS pick 03-07-23-28-36

Drawn line  04-08-23-24-34

close but no cigar

pick4 test

LRS 7-4-7-5

Draw 6-5-4-1

pick3 test

LRS 8-1-5

draw 1-0-4

overall not too hot, Cash5 showing the most promise so far.

the pick3 should be performing better, but I have thousands of steps to go through to find the right setting for that one, same with pick4 and even cash 5

I am well near done the red balls and 1/3 done the white balls in PB. I hold the highest hopes based on backtests for the Cash5. I might even go live playing the C5 in a few months (maybe ready by mid summer).

the pick3 however has the highest incident counts, where the numbers generated match the picked numbers 10% to 11% of the time, they just don't line up together very well yet. (by contrast, the red ball in PB only matches 5%, and that's been my BEST system result to date...).

my goals are to get these systems thoroughly tested and running by september and maybe I'll go all out and even play the pick3 or pick4 until december (because on december 31, if I don't have at least 1 win from any system, I'm "retiring" the spreadsheets for good and accepting my lowly lot in life as that of a 1QP powerball player for the rest of my days)

I want 1 hit for pick3 (straight shooting for p3/p4 no boxes and no mid-day)

I want 1 4/5 from C5

and I want 1 4+0 or 3+1 fromPB

any one of those happen and I'll feel like my work was worth something...

because I hold each position in a pick 3 or 4 game as a separate entity,the OOP in LoopRS does not apply, hence the abbreviated LRS designation.

Since I must do each position separately, I am probably going to go back 10 draws at a time (side by side) to speed things up a bit. Just have to modify the formulas a bit so I can have everything on one sheet for each game.

LRS3 and LRS4 will be one ticket straightshooter systems, not backtesting box hits this time. no run-downs or workouts... I get what I get.

The LRS system will be backtested then put to "paperplay" on the LP predictions page for awhile. My biggest time killer is having to reconstruct the pick3 sheet from scratch. I have from 1977 to 2005 so far.

Based on the performance of the Cash5 backtest, I kind of have higher hopes for this pick3/4 system than normal. Heck I might even put this into actual play if it tests well...

BTW my LoopRS sheet is calling for #30 to fall as the red ball tonight in PB, let's see how that goes.

Also, sometimes in an OOP system,you get duplicate numbers in a pick... I resolve this by filling inthe bet slip with the unique numbers I have and also checking the QP box,it fills in the rest... 

My LoopRS excel project gets it's numbers from previous draws. I chose this method because it incorporates the same randomness as the game itself... you're just using the most frequent positions.

Ever notice how great deals of data for a single game kind of show trends year to year? regardless of the machine changes or ballset changes? I believe it has to do with that particular game... Each game has it's own "random identity". For example,the #'s 20, 31 and 35 in PB... in 10 years they remain the hottest... and it is spread out over the 10 years, even though they change machines and ballsets every draw!

I have traditionally dismissed coincidence and looked upon randomness as a problem to be solved... this time I am embracing randomness AND coincidence. Instead of trying to beat the game with some formula or equation, I'm simply letting the past draw history pick my next draw. It is a VERY tedious process to keep going back draw after draw for each position, but I don't want to automate it, because I feel the need to SEE the system unfold one step at a time (like stepping thru a debugger).

Seeing this unfold for some reason makes me think that it really doesn't matter what machine the balls are drawn from, somehow each game has it's own identity and keeps it consistent... like how 2 states can have such widely varied pick3 draw histories.

I have tried for years to BEAT randomness, now I'm gonna relax and JOIN it for awhile ;-)

Everything in nature is trying to find balance, from us as people to the cells inside of us to the atoms everywhere...

I'm stuck on LoopRS development now, but the next one on the back burner will be based on the concept of Homeostasis

perhaps instead of looking for the "most frequent" or "least likely" we should be seeing what draw results will tend to balance out the draw frequency...

I didn't ditch PROfile, because there is much valuable data there that I just haven't figured out how to use (or backtest).

I haven't yet envisioned the form this "balance" system will take (other than an excel file), but the possibility of truly understanding the nature of the numbers intrigues me!

comments? ideas?

LoopRS backtest of PA cash5 would've paid $1 had I spent one to play... still have several thousand draws to sift thru before live tests.

Powerball RB should be sifted by this weekend, hopefully giving me something to play regularly besides the QP,that's not workin' out these days :-(

White balls should be done by summer... till then it's 1QP+PowerPlay every draw.

I'm looking forward to the test on the PA evening pick3, although the draw count is over 10K, the process for non-replacement picks (like the red ball in PB) is much quicker (no OOPs to sift thru)

now if only I could automate the sifting process...

Next system in full swing

LoopRS=

Latent out-of-position Repeating Sequences (or schedule, or any other S word that makes sense)

What this does is hunts for repeats, even out-of-position ones. For example, 12 is WB1 in one draw then comes back as WB2 in the next draw. My old format excel programs would miss this. When I tire of LoopRS, I shall keep the engine and revisit my old stat systems with this new backtest ability.

right now, I have a test sheet for Powerball, and a new one for PA cash 5 (a game I don't really play, but one with 4000+ draws and no matrix change in decades)

the hunt for the best fit must be done by hand, but the backtest is finally 100% automatic.

PB status - almost back to 2 years ago in WBs and almost 4 years ago in RBs

C5 status - back 11 draws so far, that excel file is almost 30 megs! (SLOW recalcs)

of interest is the subset LRS (Latent Repeating Sequence) for the red ball. Based on my belief that a pick 3 game is 3 sequentially unrelated draws of 1 in 10, I can take this new system to the daunting 10,000+ draw history of the venerable PA pick3 evening...

did I mention that this is my first system capable of adapting to any game?That's definitely a first for me. From now on I am dropping all the old systems I tried that cannot be backtested. I was faced with a challenge in the LoopRS backtest sheet because sometimes the picks would have doubles (or even triples), like 11 twice in one pick. A pretty clever use of a "uniqueness" check and some simple math helped me verify genuine hits only for backtest results.

I am hoping to have the PB history thoroughly checked by may, the C5 could take 'till the end of the summer! anyway, the live fire tests won't happen until I have completed the preparatory detective work currently underway.

as always the system will spit out just one number to play for the planed games....

1 PB ticket, 1 C5 ticket, 1 pick3 ticket (and if I feel particularly ambitious) 1 pick 4 ticket (evening only!, I don't do computer generated games like PA midday 3/4)

to paraphrase "Rage against the machine"

"Rally 'Round the Spreadsheet... Pocket full of Pens"

Still plugging away here...

got an access DB built with all PB sort order data. Now to build the queries...

As for the Excel sheets, big leaps in summary screen automation and updating is getting to be a quicker process... system finally generates the "quick" pick just by entering the results from the last draw.

think I'll keep 'em both!

hot indicators are definitely direction and divisibility, still awaiting inspiration on narrowing them down however.

New deadline... 1/1/2008

I'm giving myself until then to get repeatable results or I'm wiping out the files and giving up. (systems that is, will always have that 1 QP)

Ahh the challenges of designing a database.

After much thinking and a quick review of the "Designing a database" book I used to study for my MCDBA, I have come to the realization that my entire suite of excel workbooks can be reduced to 2 tables and a few carefully crafted queries... Added bonuses,

1) new filters can be added as easily as dropping in a new query.

2) once complete, a few minor changes can be made to convert to any game

3) I can backtest by simply selecting certain date ranges

I have to refresh my memory on the use of Access forms to make it feel more like an app than a db. but at least I have a plan now.

look out MUSL ;-)

The massive collection of worksheets and workbooks now occupying my "PROfile" folder will eventually become an Access Database App. By converting the summary sheets into queries, I will finally get the power to backtest using date ranges (or draw numbers).

Updating the sheets is already a formidable challenge, but I am essentially looking at an exploded database with multiple queries... I didn't know what I wanted from Access before, but it's all becoming clearer now...

There was good in the first test...

Direction... 5 of 6 went in the indicated directions

Divisibility... 5 of 6 went the indicated way (Even or Odd)

HEXade Groupings... 3 of 6 went where the clusters indicated

Next Step...

Time to add follower ranges... This will be one decent eliminator for some numbers and useless for others.

Also time to make a color coded "elimination sheet"

marking downn what reasons numbers were eliminated. I will keep a workbook full of such sheets to go back and review. Some may be eliminated on more than one filter... I should note this

Also time to continue the PROfiling... Including consistent o/e mixes and the long saught after (by me at least) "out of position repeat" counter. Most of this will not be ready for saturday, but I am giving myself no deadlines, only want one win... ;-)

The complexity of this "suite" of spreadsheets is daunting, but the ease of readability has improved 100 fold over prior attempts. Some stats update from multiple workbooks (usually I use multi-sheet formulas).

Using percentages as confidence indicators is great when deciding what to play, but even better at deciding what NOT to play, hopefully accuracy will get better over time.