FCHL Temp Home

Zach6668 · Guru

Gameday 5 - October 12, 2017

Zach6668 · Guru

Dave's double header lineup here is... not good. I'm predicting a D will be the OT player (... because the only F is Boedker and he has good D...)

jeff · Guru

Here ends the undefeated run. 4GA in the third period.

Zach6668 · Guru

Gameday 5 - October 12, 2017

Whalers 2.0 vs. Iowa Crop Dusters 2.5
BlackDevilHawks 0.0 vs. Toronto Rocket 2.5
Lethbridge Golden Seals 3.5 vs. Injured Reserve 1.0
asdfjkl; 2.5 vs. Blindsides (-2.5)
Broadway (-0.5) vs. Tulane Flyers 3.5
KC Reserves 2.0 vs. Newmarket Hurricanes 0.5
The Ales (-0.5) vs. Lost Cause 1.5
Royal Oak Fearsome Beavers 4.0 vs. BlackDevilHawks 0.0

Quick Hits

Despite their pessimism, KCR moves to 5-0.
Whalers fall again by just a half point moving to 0-5 in maybe the most unlucky start to a season ever?
Ales also at 0-5.
BDH streaks comes to a halt with a double-loss. That they even got back to 0.0 is impressive given the lineup the team ran with this evening
asdfjkl;, despite selling off everyone, manages to earn their first win of the season
Hurricanes 4-game winning streak comes to an end

Zach6668 · Guru

jeff wrote:
Here ends the undefeated run. 4GA in the third period.

no

jeff · Guru

I got very lucky.

Zach6668 · Guru

jeff wrote:
I got very lucky.

Meh. Short night. You'd have beat at least 7 teams. (Tie an 8th, don't know OT.)

Jay · Turning Back the Clock

jeff wrote:
I got very lucky.

You did get lucky. At the time you posted that I put myself at around a 75% chance to win. You faded 1.0 from 2 periods of Couture, Burns, Little, Demers and were even able to get an insurance goal from Tanev. Good game!

MLP · Guru

I dont think "pts/goals for" was something we had on the roster sheet, so I did this now just to try and keep my sanity.
So, in case anyone else cares...

Team Pts For Record
KCR 23 5-0
ROFB 16 4-2
BlackDevilHawks 14.5 4-2
Whalers 12.5 0-5
Rocket 12 3-2
LethGS 11.5 3-3
LostCau 11.5 3-2
CropDusters 9.5 3-2
Broadway 8 4-2
IR 7.5 2-3
NmkHurri 6.5 4-1
asdfjkl; 6 1-5
Blindsides 5 2-3
TulFlyers 3 2-3
TheAles 1 0-5

-- Edited by MLP on Friday 13th of October 2017 10:11:31 AM

MLP · Guru

had no idea the KC Reserves had been such a force early on. wow, thats an impressive total

-- Edited by MLP on Friday 13th of October 2017 10:12:24 AM

jeff · Guru

Not in any way sustainable. Here are my current .500 guys.

MLP · Guru

I can live with your team being better than mine, but I'm going to have a tough time getting over how much better your chart presentation was than mine.

jeff · Guru

A+ response

MLP · Guru

Seriously speaking, you have quite a few forwards that I am really high on this year.
I think guys like Schwartz, Huberdeau, Rakell are going to have career years, and big bounce backs for Tavares+Kopitar.

rinswun · Guru

Data vis is all important.

I've had two games so far where i've had 0g0a from my skaters (both times I had 6 skaters or less actual suiting up for their nhl teams). Take Ovechkin out of the mix and my farm is outsourcing my team.

jeff · Guru

MLP wrote:
Seriously speaking, you have quite a few forwards that I am really high on this year.
I think guys like Schwartz, Huberdeau, Rakell are going to have career years, and big bounce backs for Tavares+Kopitar.

I am worried about the overlap up front (StL, LA). I have a ton of nights later this month with fewer than 6 forwards, not sure if others have seen this trend as well. I would love to do a lateral trade involving Schenn for example.

Zach6668 · Guru

Who is your other LA forward?

Zach6668 · Guru

Oh nm. Toffoli.

jeff · Guru

Yeah. Toffoli kinda sucks and Kopi probably wouldn't get fair value in a trade right now. It's a dilemma.

MLP · Guru

I like Toffoli too!

Toffoli and Schwartz for _______ and ________. Look at my team, fill in the blanks! lol

Dubey · Guru

hey, for you Excel gurus. I'd like to scrape data so that my players' stats are automatically loaded into my spreadsheet like I assume you guys do. I've gotten as far as scraping the NHL stats from Yahoo onto a separate sheet in my wporkbook. How to I query a specific player's stats onto another sheet in my workbook so that it still locates that data even when it changes location in the source sheet?

-- Edited by Dubey on Friday 13th of October 2017 12:36:47 PM

Ottawa_Biatch · Guru

Dubey wrote:
hey, for you Excel gurus. I'd like to scrape data so that my players' stats are automatically loaded into my spreadsheet like I assume you guys do. I've gotten as far as scraping the NHL stats from Yahoo onto a separate sheet in my wporkbook. How to I query a specific player's stats onto another sheet in my workbook so that it still locates that data even when it changes location in the source sheet?

-- Edited by Dubey on Friday 13th of October 2017 12:36:47 PM

ctrl + alt + del

Zach6668 · Guru

Dubey wrote:
hey, for you Excel gurus. I'd like to scrape data so that my players' stats are automatically loaded into my spreadsheet like I assume you guys do. I've gotten as far as scraping the NHL stats from Yahoo onto a separate sheet in my wporkbook. How to I query a specific player's stats onto another sheet in my workbook so that it still locates that data even when it changes location in the source sheet?

-- Edited by Dubey on Friday 13th of October 2017 12:36:47 PM

You've been able to do this automatically so far? I haven't figured out how to do that in Excel, only in Google Docs. For Excel I've been manually copy/pasting the Yahoo Stats page in myself.

The rest is just a couple steps.

Maybe you need a step to clean up the data in a new worksheet, maybe not. You could probably just do it all with vlookups right from the raw data. I'll assume you don't know vlookup since you asked...

In a new worksheet, you'll have a table with your players in one column, in the next columns, whatever you want, team, gp, g, a, pts, etc, whatever you want to grab from the stats data. In each column do something like this. Say your players are in column A.

=vlookup($A1,statsworksheet!$A:$X, column number of the stat you want, FALSE)

I'll explain.

$A1 is the player you're looking up

The second step, you just go highlight the table you want to lookup from... in this case it'll be back in the other worksheet with the stats, and all of the columns starting with the name of the player (important) and as far to the right as the end of the table, even go a couple columns further, doesn't matter as long as your data is in there.

The column number starts at 1 in the first column of the table in step 2, this will be the player column, counting to the right, each column has a number. If you're looking for GP, it'll be column X or whatever.

False... just always use false.

Do that for every piece of data you want for the first player in your list - the only change you'll need to make is the column number. You can actually just do that in the top left data cell of your new table and drag across to the right. Replace the column numbers as appropriate. Once the first row is complete, you can expand the whole row all the way down to the bottom for all players. (This is because I put the $ signs in the right place - makes it an absolute reference - not super important, just makes it easier to copy formulas how you want them.

After that, you can use the GP, G, A, etc to do some math and get pool points or pool points per game, etc.

Zach6668 · Guru

(Googling vlookup will explain it better)

Dubey · Guru

Thanks Zach! I'll try that later!

Dubey · Guru

Zach6668 wrote:
Dubey wrote:
hey, for you Excel gurus. I'd like to scrape data so that my players' stats are automatically loaded into my spreadsheet like I assume you guys do. I've gotten as far as scraping the NHL stats from Yahoo onto a separate sheet in my wporkbook. How to I query a specific player's stats onto another sheet in my workbook so that it still locates that data even when it changes location in the source sheet?

-- Edited by Dubey on Friday 13th of October 2017 12:36:47 PM
You've been able to do this automatically so far? I haven't figured out how to do that in Excel, only in Google Docs. For Excel I've been manually copy/pasting the Yahoo Stats page in myself.

I think so? I got it to import the table from yahoo the first time anyway, and there's a refresh button. I'll have to see if it updates after new data is added tomorrow. I'm going to guess probably not if you haven't been able to get it going since I only learned how to scrape by googling and I didn't do anything special

rich · Overselling players since 1993

If you share how to scrape from Yahoo I'll make a workbook for you that updates stats and does all sorts of jazz.

Dubey · Guru

I'm just going to assume it's not actually going to work going forward if you excel geniuses haven't been able to do it, but I just pasted this into the import from web page and clicked on the table and clicked import.

https://sports.yahoo.com/nhl/stats/byposition?pos=C,RW,LW,D

-- Edited by Dubey on Friday 13th of October 2017 01:54:40 PM

Dubey · Guru

if I make a manual change, then click that "refresh all" button, it erases the change I made and reverts the table back to it's current form (from Yahoo)

rich · Overselling players since 1993

Beauty.

Zach6668 · Guru

Dubey wr

if I make a manual change, then click that "refresh all" button, it erases the change I made and reverts the table back to it's current form (from Yahoo)

Yeah, don't make any changes to that. Just create a new sheet that pulls its data from that. The table will always be in the same format.

Dubey · Guru

Zach6668 wrote:
Dubey wrote:
hey, for you Excel gurus. I'd like to scrape data so that my players' stats are automatically loaded into my spreadsheet like I assume you guys do. I've gotten as far as scraping the NHL stats from Yahoo onto a separate sheet in my wporkbook. How to I query a specific player's stats onto another sheet in my workbook so that it still locates that data even when it changes location in the source sheet?

-- Edited by Dubey on Friday 13th of October 2017 12:36:47 PM
You've been able to do this automatically so far? I haven't figured out how to do that in Excel, only in Google Docs. For Excel I've been manually copy/pasting the Yahoo Stats page in myself.

The rest is just a couple steps.

Maybe you need a step to clean up the data in a new worksheet, maybe not. You could probably just do it all with vlookups right from the raw data. I'll assume you don't know vlookup since you asked...

In a new worksheet, you'll have a table with your players in one column, in the next columns, whatever you want, team, gp, g, a, pts, etc, whatever you want to grab from the stats data. In each column do something like this. Say your players are in column A.

=vlookup($A1,statsworksheet!$A:$X, column number of the stat you want, FALSE)
I'll explain.
$A1 is the player you're looking up
The second step, you just go highlight the table you want to lookup from... in this case it'll be back in the other worksheet with the stats, and all of the columns starting with the name of the player (important) and as far to the right as the end of the table, even go a couple columns further, doesn't matter as long as your data is in there.
The column number starts at 1 in the first column of the table in step 2, this will be the player column, counting to the right, each column has a number. If you're looking for GP, it'll be column X or whatever.
False... just always use false.

Do that for every piece of data you want for the first player in your list - the only change you'll need to make is the column number. You can actually just do that in the top left data cell of your new table and drag across to the right. Replace the column numbers as appropriate. Once the first row is complete, you can expand the whole row all the way down to the bottom for all players. (This is because I put the $ signs in the right place - makes it an absolute reference - not super important, just makes it easier to copy formulas how you want them.
After that, you can use the GP, G, A, etc to do some math and get pool points or pool points per game, etc.

ok, got this working. Thanks for the detailed instructions. Was banging my head against the wall until I remembered that when you export from Yahoo, there's a space in front of every piece of data, so I had to add that space to my lookup query in order to get a match,

Dubey · Guru

Zach6668 wrote:
Dubey wr

if I make a manual change, then click that "refresh all" button, it erases the change I made and reverts the table back to it's current form (from Yahoo)

Yeah, don't make any changes to that. Just create a new sheet that pulls its data from that. The table will always be in the same format.

yeah I just did it to "test" the data refresh to see if it would continue to pull from Yahoo.

Zach6668 · Guru

Oh that stupid space is why I use a sheet in between to clean up the data. That's advanced excel. Lol

jeff · Guru

To cut down on the need to use extra sheets, I use index match now. There are still situations where extra stuff is needed but it’s less bad. It’s basically vlookup but without the forced left to right structure.

Zach6668 · Guru

Yeah google always says to use index match but I can never figure it out as easily as vlookup

Dubey · Guru

the data scrape seems to be working. refreshed the page today and it brought in all of last night's games.

Zach6668 · Guru

nice!