Building an NHL Model: Getting Started

There is nothing more important in DFS than correlation. In order to correctly correlate in NHL, we need to know who is playing with whom. There are sites who do most of the leg work for us, but copying all the names can be a hassle. I’ll show you a shortcut to automatically scrape NHL lines to Google Sheets.
Begin by creating a new Spreadsheet. My Spreadsheet is named “NHL” and the Sheet is named “Lines”. I suggest naming them the same so you can copy, paste my exact formulas.

Type “ANA” in cell B1, “ARI” in cell C1, etc. until you have all the teams listed. This makes for easy referencing. Copy, paste row 1 to row 31. We want our import formulas separate from the cells we rely on. We need to make split second decisions in DFS so relying on a website to update a line or Google Sheets to pull the updated line from pregame rushes could be costly.

Type “LW1” in cell A2, “C1” in cell A3, etc. Your list should look like this:

LW1
C1
RW1
LW2
C2
RW2
LW3
C3
RW3
LW4
C4
RW4
LD1
RD1
LD2
RD2
LD3
RD3
PPLW1
PPC1
PPRW1
PPLD1
PPRD1
PPLW2
PPC2
PPRW2
PPLD2
PPRD2

Copy, paste the list to cell A32.

Type “=importxml(“https://www.dailyfaceoff.com/teams/anaheim-ducks/line-combinations/”,”//span[@class=’player-name’]”)” in cell B32. Copy this formula all the way to cell AF32. Change “anaheim-ducks” in the formula to the corresponding team in the column. Go to dailyfaceoff.com and click on the team if you’re unsure of a URL. Copy, paste (only values – ctrl + shift + v is a nice Google Sheets shortcut) rows 32-59 to rows 2-29. These are the rows you’ll make last second changes to and reference with your other sheets. Keep in mind to do this copy, paste prior to lock and making adjustments. This won’t automatically update but I believe it’s a necessary step to avoid mistakes/misinformation.

*The apostrophes and quotation marks inside the formulas from the article may copy over incorrectly to Google Sheets. If you are getting a parse error with the above formula, delete the apostrophes and quotation marks then retype them.

Create a new sheet. Type “Team” in cell A1, then type out the schedule in cell A2 on down. My game sheet looks like this:

OTT
TOR
WSH
STL
VAN
EDM
S.J
VGK

I have data for goalies in columns B-K. Type “Line” in cell M1. In cell M2, type “=A$2” and copy down to cell M19. Column N should look like this:

Position
LW1
C1
RW1
LD1
RD1
LW2
C2
RW2
LD2
RD2
LW3
C3
RW3
LD3
RD3
LW4
C4
RW4

Type “Player” in column O. This is where we get our automatic input from the lines sheet. Type “=index(Lines!$A$1:$AF$19,match(N2,Lines!$A$1:$A$19,0),match(M2,Lines!$A$1:$AF$1,0))” in cell O2 and drag down.

Another important part of NHL DFS is knowing who is on the powerplay. Type “PP” in cell P1. There is probably a simpler way to do this, but this formula works to tell you if they are on PP1 or PP2. In cell P2, type “=if(index(Lines!$A$1:$AF$29,20,match(M2,Lines!$A$1:$AF$1,0))=O2,1,if(index(Lines!$A$1:$AF$29,21,match(M2,Lines!$A$1:$AF$1,0))=O2,1,if(index(Lines!$A$1:$AF$29,22,match(M2,Lines!$A$1:$AF$1,0))=O2,1,if(index(Lines!$A$1:$AF$29,23,match(M2,Lines!$A$1:$AF$1,0))=O2,1,if(index(Lines!$A$1:$AF$29,24,match(M2,Lines!$A$1:$AF$1,0))=O2,1,if(index(Lines!$A$1:$AF$29,25,match(M2,Lines!$A$1:$AF$1,0))=O2,2,if(index(Lines!$A$1:$AF$29,26,match(M2,Lines!$A$1:$AF$1,0))=O2,2,if(index(Lines!$A$1:$AF$29,27,match(M2,Lines!$A$1:$AF$1,0))=O2,2,if(index(Lines!$A$1:$AF$29,28,match(M2,Lines!$A$1:$AF$1,0))=O2,2,if(index(Lines!$A$1:$AF$29,29,match(M2,Lines!$A$1:$AF$1,0))=O2,2,””))))))))))”. Drag the formula down to cell P19. Copy the whole team down 29 times, change your cell references in the M column, and you have the whole NHL slate! You can include some iferror(xxxxx,””) to make it look good as well as add stat pulls from any NST data you have in your model.


Good luck and be sure to reach out to me with any questions!

Jonathan Gab

jgabby has been playing DFS seriously since September 2017 with his main focuses being NHL and MLB where he is consistently ranked in the top 100 on RotoGrinders.

jonathangab has 146 posts and counting.See all posts by jonathangab

Leave a Reply

Your email address will not be published.