NHL/MLB GSGA/RSRA Spreadsheet

First << 2 of 3 >> Last
Views: 2335
Posts: 53
 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#26 Report User
Posted:

Quote Originally Posted by grecycle99:

Hi buckeyekaptn, When I try to update the file, theres window popping for debugging for the query. Is it possible that came on because I use excel 2007?

Yes.  The one I use is the newest Excel 365/2016 one.  Your version didn't have the query.  A fix coming.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#27 Report User
Posted:

Quote Originally Posted by grecycle99:

Hi buckeyekaptn, When I try to update the file, theres window popping for debugging for the query. Is it possible that came on because I use excel 2007?

I didn't delete the old version, just changed the name and hid it.  Unhide NHL Odds1.  What you're going to do is put "1" in certain areas.  Save your copy, so if there's a screw up, you can just exit out and not save it...restart again.  I don't recall that version well so I hope my directions work for you.

Get into Visual Basic.  For me, that's under Developer to the left. Click on Data Module.  Scroll down to UpdateNHLOdds1 ().  Halfway down, change NHLOdds.Range("A2").PasteSpecial xlPasteValues to NHLOdds1.Range("A2").PasteSpecial xlPasteValues (adding the 1).

In Support Module, right at the top, add "Global NHLOdds1 As Worksheet" and under SetWorksheets () add "Set NHLOdds1 = Worksheets("NHL Odds1")". You can copy and paste without the quotes right into VBA. 

One more post coming...DON"T HIT SAVE!!!

 

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#28 Report User
Posted:

Quote Originally Posted by grecycle99:

Hi buckeyekaptn, When I try to update the file, theres window popping for debugging for the query. Is it possible that came on because I use excel 2007?

Still in VBA, click on Sheet9 (NHL) and change in Private Sub All_Click () ....halfway down

Call UpdateNHLOdds to Call UpdateNHLOdds1

and in Private Sub Odds_Click()

Call UpdateNHLOdds to Call UpdateNHLOdds1

 

Now, go into NHL Odds1 and CLEAR NOT DELETE Column B.  Notice in the shaded area Col AO are the rotation numbers, top two at 35.  Changes these to 1 and 2 to complete the sequence. If any rotation numbers on any website aren't in any of the shaded area of AO, make the change on AO.  

Still not saving, go into NHL tab and click on Odd.  If everything shows up properly, I remembered everything and you can save now. I will be doing the same update for MLB.  Do the same fix for that sport when it happens.  I named all macros similar between NHL and MLB so you just have to sub MLB for NHL. Let me know if any more problems. 

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#29 Report User
Posted:

I had completed the backtests for MLB which will include the -1.5 parlay plays.  Then I noticed numerous discrepancies on the worksheet/template I use. 

new_smh

I have to go back and redo all calculations for each year.  Plus, being this template was a copy from NHL, the backtests from that sport are incorrect too.  I will go back and correct NHL.  The good thing is that I made the formulas for the -1.5 parlays and I will add NHL's -1.5 backtest to the sheet.

 

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#30 Report User
Posted:

This is to grecycle99's problem.  Not this coming update with the backtests, but once I figure out how to do it, I will post an update that will give the odds for those that have the older Excel programs or a work around to avoid the Query Connection.

 
Prospect
Participation Meter
Joined: Jan, 2010
Posts: 178
#31 Report User
Posted:

Thanks buckeye for your hard work.

 

Wishing you a happy, healthy and succesfull new year :)

The same to everybody on here.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#32 Report User
Posted:

Quote Originally Posted by BuckeyeKaptn:

https://drive.google.com/file/d/1PyV2dUzr9C4hf4ovSuFDoEg7F3nMMUZs/view?usp=sharing

I have uploaded a new version.

This one has different tabs/sheets that can be used for those that have an older version of Excel, such as grecycle99.  These tabs have a "1" after the name.

I tried to organize the sheets a bit, it's a work in progress.  I'm going to eventually dim the colors a bit.  I find that I do get colorful when I make a workbook.

For NHL, I have added the -1.5 run line stats on the NHL Systems tab.  As Covers does not have run lines archived, I took the ML parlay lines (which I make by a formula with the money lines) and added +350.  Using a web based parlay calculator, I found that this number is significantly lower than what a RL Parlay would pay out over a ML Parlay (usually +400 to +500, I found).

I have the last 5 years back test for MLB for ML and RL parlays added in.  Each type is on it's own tab/sheet.

Same for NHL, except that 2013 to 2016 years aren't complete for RL, and the same years for ML need to be rechecked.  So, only 2017/8 is done.

I think this should do it until MLB starts.  I'll clean up/organize the look of the pages, finish the back tests and make sure my Query for MLB odds is correct before releasing the next version.

If I find anything that will help for NHL, I will do a version for that.  I was thinking of adding a road/home strength of schedule to the 2 sheets.

One last thing.  There was a problem once I started with the Query connection where the previous days plays were posted instead of the "today's plays".  It was fixed.  I had to uncheck the background refresh in properties.

Again, any problems, let me know.  I don't check the web site daily but at least every few days.  Now that Christmas is over, I have time to fix any problem (I'm a USPS mailman, man in blue).

Good wagering!!!

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#33 Report User
Posted:

I was going to update the backtests on this thread's spreadsheet, adding different components. I then decided to just make a new spreadsheet with my computations on it.  Basically, I combined all sheets into one and am presenting it to you.  Currently only for MLB.  I'll upload and update when NHL is added.  I will also remove the backtest sheets from Sports 18.

https://drive.google.com/file/d/1xqZP5yYduTfJADk37lPRGBhb2-kF1yJn/view?usp=sharing

This is an interactive sheet, in that you can change different aspects to get data you want.  Change only cells that are in yellow. The two type of parlays are Top 2 and Top 2 ML (Home, Away, Both and -1.5 RLs). You can change the RSRA Dif number to view parlays that are based on this number and higher.  Change to 0 to view all plays.

You can get certain data for inclusive dates (only RSRA plays not the parlays).

This spreadsheet does not pull data from the internet, can be read, edited offline.

 
Prospect
Participation Meter
Joined: Jan, 2019
Posts: 1
#34 Report User
Posted:

Just set up an account to come on here and thank you BuckeyeKaptn :D

The GSGA really helps with my Salami and First Period Goal wagering.  

 

So many thanks.  I look forward to seeing how the MLB spreadsheet looks

 

GL 

 
Prospect
Participation Meter
Joined: Jan, 2010
Posts: 178
#35 Report User
Posted:

Quote Originally Posted by projectsmith:

Just set up an account to come on here and thank you BuckeyeKaptn :DThe GSGA really helps with my Salami and First Period Goal wagering.     So many thanks.  I look forward to seeing how the MLB spreadsheet looks   GL

What parameters you use for your 1P and salami wagers?

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#36 Report User
Posted:

Quote Originally Posted by projectsmith:

Just set up an account to come on here and thank you BuckeyeKaptn :DThe GSGA really helps with my Salami and First Period Goal wagering.     So many thanks.  I look forward to seeing how the MLB spreadsheet looks   GL

I'm glad it's helped.  Yeah, Spring Training is around the corner.  I just have to add (I believe) MLB to the backtest spreadsheet.  Everything else is pretty much done.  Just need to check the daily update at the beginning of the season.

 

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#37 Report User
Posted:

Quote Originally Posted by BuckeyeKaptn:

Quote Originally Posted by projectsmith:

Just set up an account to come on here and thank you BuckeyeKaptn :DThe GSGA really helps with my Salami and First Period Goal wagering.     So many thanks.  I look forward to seeing how the MLB spreadsheet looks   GL
I'm glad it's helped.  Yeah, Spring Training is around the corner.  I just have to add (I believe) MLB to the backtest spreadsheet.  Everything else is pretty much done.  Just need to check the daily update at the beginning of the season.

Nope, I have to update The NHL to the back test sheet.  My bad.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#38 Report User
Posted:

Covers changed the URLS for all teams but currently Blue Jackets won't post.  I have been waiting for MLB and now I have to go change the urls. Dagnabbit

 
Prospect
Participation Meter
Joined: Jan, 2010
Posts: 178
#39 Report User
Posted:

Hello my friend,

 

Should I replace all the teams urls by this url : https://www.covers.com/sport/hockey/nhl/2018-2019/team/winnipeg-jets for each teams?

Thanks

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#40 Report User
Posted:

Quote Originally Posted by grecycle99:

Hello my friend,   Should I replace all the teams urls by this url : https://www.covers.com/sport/hockey/nhl/2018-2019/team/winnipeg-jets for each teams? Thanks

 

Yep.  I haven't had the time, so I'm trying to do it before opening day for MLB and then October for NHL.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#41 Report User
Posted:

Quote Originally Posted by grecycle99:

Hello my friend,   Should I replace all the teams urls by this url : https://www.covers.com/sport/hockey/nhl/2018-2019/team/winnipeg-jets for each teams? Thanks

Yes, if you go to each team's past results and look and copy that url into column E for that team...that'll work until I fix it so all you have to change is the year like previously.  Don't worry about the formula you will be replacing. This is all in the hidden URL tab for anyone else that wishes to do this.

Covers has redesigned the look of the team page too.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#42 Report User
Posted:

Quote Originally Posted by BuckeyeKaptn:

https://drive.google.com/file/d/1xqZP5yYduTfJADk37lPRGBhb2-kF1yJn/view?usp=sharing

New update. NHL fixed. MLB works.

Currently, Covers for MLB Teams has not changed the web format, continuing to be the old format.  Hopefully, if it changes like the NHL, I will have time to jump on it and get an update out.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#43 Report User
Posted:

Quote Originally Posted by BuckeyeKaptn:

Quote Originally Posted by BuckeyeKaptn:

https://drive.google.com/file/d/1xqZP5yYduTfJADk37lPRGBhb2-kF1yJn/view?usp=sharing
New update. NHL fixed. MLB works. Currently, Covers for MLB Teams has not changed the web format, continuing to be the old format.  Hopefully, if it changes like the NHL, I will have time to jump on it and get an update out.

Another new update AGAIN as the plays didn't properly post over on MLB Systems.  Happens at the beginning of every year.

 
Prospect
Participation Meter
Joined: Jan, 2010
Posts: 178
#44 Report User
Posted:

Quote Originally Posted by BuckeyeKaptn:

Quote Originally Posted by BuckeyeKaptn:

Quote Originally Posted by BuckeyeKaptn:

https://drive.google.com/file/d/1xqZP5yYduTfJADk37lPRGBhb2-kF1yJn/view?usp=sharing
New update. NHL fixed. MLB works. Currently, Covers for MLB Teams has not changed the web format, continuing to be the old format.  Hopefully, if it changes like the NHL, I will have time to jump on it and get an update out.
Another new update AGAIN as the plays didn't properly post over on MLB Systems.  Happens at the beginning of every year.

Thank you Buck

I don't know if it's only me but this link seam to reffer to the backtest google sheet?

 

Thanks for all your work my friend

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#45 Report User
Posted:

Quote Originally Posted by grecycle99:

Thank you Buck I don't know if it's only me but this link seam to reffer to the backtest google sheet?   Thanks for all your work my friend

Thanx, no I hadn't really paid attention to the correct link...here it is:

https://drive.google.com/file/d/1PyV2dUzr9C4hf4ovSuFDoEg7F3nMMUZs/view?usp=sharing

I updated through today's (Saturday's) games.  Just an FYI to everyone, once the teams that haven't played on the road or home do so, the RSRA will be invalid (#NA or #VALUE) and will not post to MLB Systems.  I should have waited to post the update until then and I will repost after anyways for the novice excel people.

I checked the above link and it's the correct one.

Thanx Grecycle

 
Prospect
Participation Meter
Joined: Sep, 2016
Posts: 2
#46 Report User
Posted:

Good morning. First let me say thank you for the hard work you have done in this spreadsheet. I do seem to be having a problem with the update feature. When press the "All" tab to update it will start to update but when it gets to around team 26 St. Louis it freezes up and excel stops responding. Can you please help to figure out what I am doing wrong with the updates. Thank you in advance.

 
Prospect
Participation Meter
Joined: Sep, 2016
Posts: 2
#47 Report User
Posted:

BTW I am using Excel 2016.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#48 Report User
Posted:

Quote Originally Posted by LVDean:

Good morning. First let me say thank you for the hard work you have done in this spreadsheet. I do seem to be having a problem with the update feature. When press the "All" tab to update it will start to update but when it gets to around team 26 St. Louis it freezes up and excel stops responding. Can you please help to figure out what I am doing wrong with the updates. Thank you in advance.

I am having the same problem, I have the same version as you.  I have been wondering if my wifi has been given out or something.  When I get home from work and do it (like 10 minutes ago), there's no problem.  I'm off tomorrow and I was going to wait until a little bit later to update, see if that matters.  I'm also going to save and exit each time to see if that helps too.  I usually just keep the file open and not exit...updating everyday, saving every now and then.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#49 Report User
Posted:

Quote Originally Posted by LVDean:

Good morning. First let me say thank you for the hard work you have done in this spreadsheet. I do seem to be having a problem with the update feature. When press the "All" tab to update it will start to update but when it gets to around team 26 St. Louis it freezes up and excel stops responding. Can you please help to figure out what I am doing wrong with the updates. Thank you in advance.

I'm also thinking about doing the query connection for every team instead of the web scraping as I have now.  That would only work for the newer excel versions as ours.

 
Veteran
Participation Meter
Joined: Jan, 2013
Posts: 1440
#50 Report User
Posted:

Quote Originally Posted by LVDean:

Good morning. First let me say thank you for the hard work you have done in this spreadsheet. I do seem to be having a problem with the update feature. When press the "All" tab to update it will start to update but when it gets to around team 26 St. Louis it freezes up and excel stops responding. Can you please help to figure out what I am doing wrong with the updates. Thank you in advance.

So far, before I update....if I save and restart the file, it will fully update.  If I just hit save and then try to update without exiting, the file will freeze. This is a new problem and I don't know why.  I always used to update every morning, saving occasionally and never exit.

First << 2 of 3 >> Last
Desktop View: Switch to Mobile View