{"id":6134,"date":"2019-07-25T17:50:23","date_gmt":"2019-07-25T09:50:23","guid":{"rendered":"http:\/\/www.finereport.com\/en\/?p=6134"},"modified":"2019-07-25T17:52:59","modified_gmt":"2019-07-25T09:52:59","slug":"i-made-a-dynamic-hurricane-map-with-excel%ef%bc%81","status":"publish","type":"post","link":"https:\/\/frg.fineres.com\/en\/2019\/07\/25\/i-made-a-dynamic-hurricane-map-with-excel%ef%bc%81\/","title":{"rendered":"I Made a Dynamic Hurricane Map with Excel\uff01"},"content":{"rendered":"\n

These days I have been obsessed with researching data maps<\/a>. I challenged myself and made a data map using Excel. The graphic below is the dynamic map of Hurricane Irma that I have drawn with Excel.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

If you are interested, I will be happy to share with you the process of making a hurricane map with Excel. It is to use the bubble chart to outline the dynamic path of the hurricane and to show the change in wind strength. Here are the specific steps for making a hurricane map.<\/p>\n\n\n\n

1. Prepare Materials<\/strong><\/h2>\n\n\n\n

\u2460 Find a map of the US Atlantic that includes lines of latitude and longitude.<\/p>\n\n\n\n

Note that the map we need must have latitude and longitude lines. Many maps provided on the public network are inaccurate. I downloaded this map from National Hurricane Center and Central Pacific Hurricane Center<\/a>.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

\u2461 Download the storm track statistics of Hurricane Irma from Weather Underground<\/a>, including date, time, latitude, longitude, wind, and so on.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

2. Process Data<\/strong><\/h2>\n\n\n\n

\u2460 Remove the units of data such as latitude, longitude, wind speed, wind pressure, etc. And the date and time should be converted into a format that is easy for Excel to process.<\/p>\n\n\n\n

\u2461 We can see that the hurricane statistics are recorded every six or three hours in the data material. Here we keep the data recorded every six hours.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

3. Draw a Bubble Chart<\/strong><\/h2>\n\n\n\n

\u2460 Insert the above map into the table, and then draw a bubble chart on it. The X axis of the bubble chart represents the longitude data, the Y axis represents the latitude data, and the bubble size depends on the value of the wind pressure.<\/p>\n\n\n\n

\u2461 Design the format of Chart Area. The maximum and minimum values of the coordinate axes in the bubble chart are set according to the latitude and longitude readings. And make the spacing on the coordinates coincide with the spacing of the latitudes and longitudes on the map. Here I set the spacing to 5, which ensures that the data points drawn by the bubble chart match the actual latitude and longitude on the map.<\/p>\n\n\n\n

\u2462 Drag and drop the border of the plot area to make it coincide with the coordinate axes in the bottom Atlantic map. Then hide the axis data and set the border to “No line”.<\/p>\n\n\n\n

4. Make <\/strong>Dynamic Effects<\/strong><\/h2>\n\n\n\n

To achieve the dynamic effects of the hurricane trajectory, we can use Slider Control <\/strong>to control the time. Each time we slide, the time is increased by 6 hours. The cell linked by the slider passes the data to the table area on the left side of the chart. And then it obtains the corresponding latitude and longitude data and wind data by querying the time point. At the same time, two sets of data sources of the chart are generated from this time data. The entire control chain is thus formed and the final control of the chart by the slider is completed. <\/p>\n\n\n\n

Further than that, if I want to implement automatic control of the slider, I will need VBA code to make the chart automatically show dynamic effects. <\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Operation Process:<\/strong><\/h3>\n\n\n\n

\u2460 Write two macros with VBA. One macro controls the start, the other controls the stop.<\/p>\n\n\n\n

\u2461 Draw 4 controls, representing start, stop, loop, and slider. Specify the above macros.<\/p>\n\n\n\n

\u2462 The format of the slider control specifies the left cell, which records the sliding data. And the upper cell \u201cData & Time\u201d calls the data of this cell.<\/p>\n\n\n\n

\u2463 Prepare dynamic data.<\/p>\n\n\n\n

In order to achieve the above dynamic effects, two sets of data are actually prepared in the bubble chart. One set shows all the path points that the hurricane has traveled before the current time point, and the other set marks the location of the hurricane at the current time point.<\/p>\n\n\n\n

Here we need to use the LOOKUP function and call the data of the cell \u201cData & Time\u201d.<\/p>\n\n\n\n

For the first set of data, we select all the data less than or equal to the current time point and set the other data points to #N\/A.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

For the second set, we select the data that matches the current time, and the other data is also set to #N\/A. Here, #N\/A doesnt display data points in the chart.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

\u2464 Bind the bubble chart to the data source.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n

In the final step, we bind the bubble chart to the data source and the hurricane trajectory map is complete. <\/p>\n\n\n\n

The textbox in the chart can get the information of the data points directly from the cells by linking with them. In addition to the bubble chart, a set of column charts is added to the map to show the wind, and its data binding operation form is the same as that of the bubble chart.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

Well, the general idea of making a hurricane map with Excel is like this. Below is an official map of Hurricane Irma. Is it very similar to the one I made with Excel?<\/p>\n\n\n\n

\"\"
From <\/em>Weather Underground<\/em>\ufeff<\/a><\/figcaption><\/figure>\n\n\n\n

Tips<\/strong><\/h2>\n\n\n\n

Excel is very powerful, but if you want to use it to make some complicated charts, you must have a code base and learn VBA language, which is time consuming. I shared an article 4 Uses of Data Maps in Business Analysis<\/a><\/em>, in which all maps are made with the zero-code visualization tool FineReport<\/a>. The operation is very simple. If you don’t understand the code, you can try this tool to create data visualization charts.<\/p>\n\n\n\n

Follow FineReport Reporting Software<\/a> on Facebook to learn more about data visualization!<\/em><\/p>\n\n\n\n

You might also be interested in\u2026<\/strong><\/h2>\n\n\n\n

\u00a0Top 16 Types of Chart in Data Visualization<\/a><\/p>\n\n\n\n

 How Can Beginners Design Cool Data Visualizations?<\/a><\/p>\n\n\n\n

A Beginner\u2019s Guide to Business Dashboards<\/a><\/p>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

This post is a step-by-step guide to making a dynamic hurricane map with Excel.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[854],"tags":[118,117],"_links":{"self":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/6134"}],"collection":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/comments?post=6134"}],"version-history":[{"count":14,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/6134\/revisions"}],"predecessor-version":[{"id":6149,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/6134\/revisions\/6149"}],"wp:attachment":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/media?parent=6134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/categories?post=6134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/tags?post=6134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}