Web Scraping Sailboat Reviews Using Power Query

Quote of the Day

The real problem of humanity is the following: we have paleolithic emotions; medieval institutions; and god-like technology.

E.O. Wilson


Figure 1: The Seawind 1600 was a top vote getter.

Figure 1: The Seawind 1600 was a top vote-getter. (Source)

While it is true that I worked on US Navy contracts for twelve years and spent some time on ships testing new underwater vehicles, I know very little about recreational boating. However, I have always been fascinated by sailing, though this fascination has been limited to reading books about the Age of Sail.

Recently, my wife and I have become huge fans of three sailing channels on Youtube that may cause me to take up sailing when I retire (about five years away). Here are my reviews of these three channels:

  • Sailing Ruby Rose
    Nick and Terysa and their Southerly 38 monohull sailboat seem to have the most diverse sailing experience. For example, I was utterly fascinated with how they took down their mast, pulled up their keel, and used their sailboat to cruise the canals of France. Their style of presentation is the most journalistic of the three shows in that they matter-of-factly present a large amount of interesting information.
  • Gone With the Wynns
    Jason and Nikki Wynn sail the Pacific with their Leopard 43 catamaran sailboat, which they purchased before learning to sail. I could never imagine myself being so bold.  The show is the best produced of the three programs. Nikki is a first-rate narrator and Jason is a professional photographer. His skills really show. The show is beautifully filmed and edited. His use of the drone for aerial shots is superb. These two are fun to watch because you get to see them learning as they go. I am particularly impressed with their worldwide network of helpers.
  • The O'Kelly's
    Nick and Megan O'Kelly and their Leopard 46 catamaran sailboat cruise the east coast of the US and the Carribean. Nick is a lifelong sailor and former TV weatherman. He has significant presentation skills and his talks are the most technical in terms of navigation and weather. Since my life is a celebration of detail, I love seeing his explanations of weather and how he plans sailing routes. Megan appears both in their sailing videos and she also has her own video channel where she covers non-sailing topics, like health and sewing. I enjoy this channel as well. I particularly like her sewing projects (my sewing has been limited to a few upholstery projects and a quilt).

Sailboat Survey

The Ruby Rose crew is selling their current boat and did a very interesting analysis of the boats they were considering purchasing as a replacement. Not only did Nick and Terysa review the boats, but they invited their viewers to review the boats.

Here are links to the sailboat review pages. While a good format, I wanted to see all the results in a cross-tabulated table.

Table 1: Sailboats Reviewed By Ruby Rose.
St Francis 50 Majestic 530 Seawind 1260 Knysna 500SE Maverick 440
Balance 526 Antares 44 GS Xsquisite X5 Discovery 50 Excess 12
Catana 53 Outremer 51 Privilege 5.0 Neel 47 NautiTech 46
Leopard 45 Astrea 42 Seawind 1600 Lagoon 42

Because the data is scattered across nineteen pages, I decided that I needed to do a bit of web scraping using Power Query. You can download my Excel workbook here.

The boats were assigned grades (1-10) in the following categories:

  • Performance
  • Interior Design
  • Build Quality
  • Safety and Design
  • Value for Money

The final grade was assigned by simply summing the total scores, which implicitly gives each category an equal weight. I wanted my data in a spreadsheet so that I could weight the categories differently. For example, I would like to give Safety and Design and Build Quality more weight than Performance and Interior Design.

Audience Assigned Grades

Table 2 shows the results from their survey of people who watch their channel. Each boat had been the subject of a separate video that allowed their audience to get a good look at each boat.

Table 2: Ruby Rose Sailboat Reviews

BoatPerform-
ance
Interior DesignQualitySafety/
Design
ValueTotal
Xsquisite X55.307.808.508.206.1035.90
Seawind 16007.806.807.607.606.0035.80
Balance 5268.006.807.707.604.4034.50
Seawind 12606.906.206.807.107.1034.10
Knysna 500SE6.206.807.007.206.9034.10
Outremer 518.106.407.606.905.0034.00
Privilege 5.05.107.608.707.904.6033.90
St Francis 505.807.007.907.705.3033.70
Leopard 455.607.006.807.206.3032.90
Discovery 505.007.308.107.604.6032.60
Maverick 4405.706.107.207.106.5032.60
Catana 53.7.206.507.106.604.9032.30
NautiTech 466.306.706.506.306.2032.00
Antares 44 GS4.906.208.107.804.8031.80
Lagoon 424.706.906.306.606.1030.60
Majestic 5304.306.907.206.905.3030.60
Astrea 425.006.805.105.905.2028.00
Excess 124.405.004.905.204.5024.00
Neel 476.804.302.905.104.0023.10

Nick and Terysa's Vote

Nick and Terysa also shared the grades they assigned to the boats, which I show in Table 3.

Table 3: Ruby Rose Crew Scoring.

BoatPerform-
ance
Int. DesignQualitySafety
Design
Value Total
Seawind 160098910642
Balance 526981010542
Outremer 5189108540
Catana 53.8998539
Knysna 500SE6899739
Xsquisite X52101010638
Majestic 53041099638
Seawind 12607789738
Maverick 4405799636
St Francis 50551010636
Discovery 50310109436
Privilege 5.058109335
Antares 44 GS471010334
NautiTech 466876633
Lagoon 424687530
Leopard 454767529
Astrea 424846426
Neel 478616526
Excess 123566424

 
This entry was posted in Excel, Personal. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *