fbpx

Hui’s World ! – An Excel Project of Global Scale.

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Following the completion of my 3D Dancing Pendulum in 2011 I have had three follow up Excel projects in mind.

This post explores the first of them, Hui’s World.

Hui’s World is an animated Excel Chart which displays the Earth and key features.

 

Hui’s World consists on an Excel chart comprising 111 series containing 2,775 data points. The 111 series are supported by 380 Named Formula. The rotation is driven by 10 lines of VBA code.

Hui’s World displays the following items:

  • The planar projection of the World as a Sphere with a select number of countries/continents;
  • The lines of Latitude and Longitude, International Date Line and Greenwich datum line (Prime meridian) and Arctic/Antarctic circles;
  • Major cities throughout the world;
  • The location of all the Chandoo.org Excel Ninja’s;
  • The States boundaries and State Capital cities in Australia;
  • Labels of Latitude, Longitude and Key Global points and optionally;
  • A User Defined Location (Marker and Label)

The globe can be rotated manually or animated with user control of the speed and direction of the rotation.

Many of the above items can be toggled to be displayed or be hidden, even as the globe turns using toggle buttons.

The Country boundaries, Cities and Lines of Longitude can be shown as if the earth is transparent or they can be hidden as they pass around the back of the earth from your view point.

There are a number of predefined views for major countries/continents and other features.

This post will look at how Hui’s World was constructed.

It will not be focusing on the math behind model, but that will be mentioned from time to time where it is important.

Apology: In simplifying the world to 2,775 points, compromises have been made. So if your Country, City etc is missing, simplified or merged to your neighboring countries I apologize in advance.

Download

If you want to download Hui’s World click here: Download Hui’s WorldDownload Hui’s World Pre Excel 2007

If you want to download the Helper File click here: Download Hui’s World Helper File


Coordinate System

Hui’s World uses an Excel Scatter Chart as the basis of the Chart.

Excel scatter charts do not understand latitudes and longitudes, they only understand a standard orthogonal x & y grid. As such we need to convert the data into the system that Excel understands.

Excel performs the trigonometry functions according to the Anti-clockwise rule.

The World uses a similar coordinate system where the base or 0 Azimuth is the Greenwich time line. All Azimuth values are taken from here.

As such we can use the Latitude and Longitudes for the locations and apply some math to work out where things should be plotted.

As such we need to determine the Location of each point of each data item and how it will be plotted on a 2D X/Y plane. I have assumed that we will look at the globe from a point level with the equator.


Data

Data was required for the location of all the elements required to be displayed. Data for the project was sourced from freely available sources.

This includes:

DataSource
Countrieshttps://www.naturalearthdata.com/
http://thematicmapping.org/downloads/world_borders.php
Citieshttps://www.worldatlas.com/travelaids/citysearch.html
Circles of the Earthhttps://en.wikipedia.org/wiki/Circle_of_latitude
Date Linehttps://ithoughthecamewithyou.com/post/international-date-line-longitude-latitude-coordinates

Even though the Country data is Low Resolution, the original file still contained 26,264 points.

Before I even started I knew that I would have to simplify this data set, if for no other reason than moving twenty thousand points in an Excel chart would have speed implications.

As a Mining Engineer I have access to a number of mining / GIS related software tools.

I used one of these, to allow me to import the various GIS files, edit the files and save the edited data out as a CSV file type.

I could have also used any CAD style program just as simply.


World Coordinates

The World Country Co-ordinates file was a CSV File containing the Latitude and Longitude coordinates and Names for all the worlds countries.

As a CSV File I imported it into a mine planning package as X & Y values.

Next I manually digitised around the key countries and continents I wanted to display. Whilst digitising I snapped to points to ensure a basic level of geographical accuracy.

This resulted in a simpler file that now only contained 1,400 points.

The new simpler file was written out as a CSV file.

Most CAD package could have been used to perform this.

In fact a number of Free Online websites also offer SHP to CSV file conversion

eg: https://mygeodata.cloud/converter/shp-to-csv

Working with the Latitudes and Longitudes in an X/Y Display system results in the file being displayed in an Equirectangular projection, with exaggerated distances in the Lower and Higher Latitudes. ie: Greenland isn’t that large. In this case that doesn’t matter as the resultant file is saved in Latitudes and Longitudes and it is the Latitudes and Longitudes that are read into Excel and then converted to X/Y values on a Spherical projection where they appear in the correct locations.

Australian Coordinates

I repeated the process for Australia and included the states. This took the source file with 108,000 data points down to a manageable 210 data points.

This data was also written out as a CSV file.

These CSV File contained columns of Line ID, Latitudes, Longitudes and sometimes other data.

ie: ID 1 in the first column above is Western Australia and ID 2 is Northern Territory etc

International Time Line and Major Cities

I used a similar process to that described above downloading a set of co-ordinates for the International Date Line as well as Major Cities of the world.

Chandoo.org Ninja Location Data

The Chandoo.org Ninja locations was sourced from Chandoo.org and the Cities coordinates manually sourced from World Atlas.com and saved in Excel.


Import and Standardisation of Data

The text files above were imported into Excel as Comma Delimitered Text Files and cleaned up.

Header and Footer lines were removed, Segment break lines were cleared and The Names were shifted to the First Column.

Next was to standardise the data.

The GIS data downloaded came in a number of formats. Most typically Longitudes are expressed in terms of Degrees East (positive) and Degrees West (negative) from the Prime Meridian.

For the purposes of use in Excel it was simpler to simply have the Longitudes expressed as degrees of a circle ie: starting at 0 deg at the Prime Meridian and extending to 360 deg. I choose to use East as Positive as I am used to having Perth as +115 Deg in my day job.

This was done using a helper column where required.

Eg: E3: =360+D3

Similarily with Latitudes, some data was sourced where zero degrees was North pole and 180 deg was the South pole.

See Column B below

The data was standardised to have Zero degrees at the equator with positive 90 Deg at the North Pole and –90 Deg at the South Pole.

Once again helper cells were used if required to transform the data to this format.

Note the Formula in C1043: =90-B1043

Any system of locations could have been used. I just choose to use what I am familiar with. With other coordinate systems the math would need to be set up accordingly.


Use of Named Formula

To add animation to the system we will need to have the following data sets

  • Original data, Latitudes and Longitudes
  • Transformation data, Details of the required view azimuth
  • Transformed data, The transformed data after the Transformation data is applied to the Original data.
  • Projected data, The Projection of the transformed data onto a 2D viewing plane

Once we have the projected data we can plot the data.

By changing the transformation data above and recalculating the worksheet, the chart will update and give the user the impression of animation.

But for this to work smoothly it needs to be fast.

One of the main skills I learned from completing [sic] the Excel Hero Academy was the benefit of using Named Formula. Named Formula store data in arrays that are stored in Memory. As such they don’t need to access the Excel grid and so performance is significantly improved.

But as I already have point data for 25 countries, 7 Australian States, 36 Lines of Longitude and 17 lines of Latitude, Cities of the World, Australian Capital Cities and the Chandoo.org Ninjas, and need between 4 and 5 sets of data for each plotted line, resulting in a total of 374 Named Formula, I could see that if I wasn’t organised and methodical this could become a real mess.

What was needed was a Named Formula Naming Convention.


Named Formula Naming Convention

To plot a line on a Scatter Chart within Excel we need to obtain the X & Y coordinates for each point along the line.

The data we have imported is a series of Latitude and Longitudes for each point.

Each line on the chart will be a Chart Series and will be made up of an array of x and an array of y values

Each Array of x and y values will be derived from the appropriate Latitudes and Longitudes. And each series may need a list (array) of values for annotation, eg: City names.

The following naming convention was setup and used for the project

Data type_Name_Type of data

Hence

Data Types used:

Countries,           C

Cities,                  Cit

Ninja’s                 Nin

Latitude Line,   Lat

Long Lines,        Lon

Other Lines         o              Other data

Variables                          Used to store variables to interact between the user controls, VBA and the Named Formula

Boolean               b             Boolean switches to toggle controls on/off

Names used:

Africa, Japan, etc

 

Type of Data:

Latitude               Lat

Longitude            Lon

X Value                 x

Y Value                 y

Names                  Nam

A Separator of an _ (underscore) was used to separate the fields

Example:

Countries:

C_Africa_Lat

C_Africa_Lon

C_Africa_X

C_Africa_Y

Cities:

Cit_CoW_Lat

Cit_CoW_Lon

Cit_CoW_X

Cit_CoW_Y

Cit_CoW_Nam

Lines of Latitude

Lat_010N_Lat

Lat_010N_Lon

Lat_010N_x

Lat_010N_y

Lat_010S_Lat

Lat_010S_Lon

Lat_010S_x

Lat_010S_y

Lines of Longitude

Lon_010_Lat

Lon_010_Lon

Lon_010_x

Lon_010_y

The benefits of using a Naming Convention like this is that in the Name Manager with Excel the Names are grouped and sorted according to there names. As such it simplifies the editing of formula.


Transforming the Data

We can transform the data from Latitudes and Longitudes to X & Y values using some simple math.

Pic

X = COS(RADIANS(C_Alaska_Lat))*Sin(RADIANS(C_Alaska_Lon + View_Az))

Y = SIN(RADIANS(C_Alaska_Lat))

So using Alaska as an example

The following formulas were added next to the top of the data set

You will notice in the above formula that we need to have a Named Formula setup to store and access the Latitudes and Longitudes for each data set.

Once again to allow for maximum performance it was chosen that the Latitudes and Longitudes would be stored as Named Formula, rather than being accessed directly from Excel ranges.

Using Alaska as an example

C_Alaska_Lat      ={55.122;57.643;59.151;58.655;60.555;62.417;63.869;65.189;66.448;68.351;70.307;71.163;69.968;69.642;60.307;60.899;57.604;54.941;51.213;55.122}

C_Alaska_Lon     ={196.677;202.293;203.221;198.887;194.575;195.364;199.223;193.038;198.399;193.172;198.058;204.413;215.048;218.997;219.004;212.925;207.849;200.566;180.895;196.677}


Named Formula

Setup the Named Formula

We can see above that we need to establish at least 4 Named formula and sometimes 5 for each item we wish to plot.

The fifth Named Formula will hold the City Names etc for annotation, where required.

Using Alaska as an example 8 formula were added adjacent to the Country data

These allow for the following constructions

Column A contains the name of our country, in this case Alaska

Column B contains a line identifier that is an artifact from the CAD processing. It has been maintained so that countries could be separated and identified

Column C & D are the Latitudes and Longitudes of the points which were digitised for Alaska

Column E Is the corrected Longitudes of the points which were digitised for Alaska going from 0 to 360 deg

Column G is a list of Named Formula names. Each is constructed suing a formula.

G3:         C_Alaska_Lat     =”C_”&A3&”_lat”

G4:         C_Alaska_Lon    =”C_”&A3&”_lon”

G5:         C_Alaska_x         =”C_”&A3&”_x”

G6:         C_Alaska_y         =”C_”&A3&”_y”

Column H is a series of formula that will setup the formula for the Named Formula

H3:        =”={“&Concat(C3:C22,”;”)&”}”

={55.122;57.643;59.151;58.655;60.555;62.417;63.869;65.189;66.448;68.351;70.307;71.163;69.968;69.642;60.307;60.899;57.604;54.941;51.213;55.122}

H4:        =”={“&Concat(E3:E22,”;”)&”}”

={196.677;202.293;203.221;198.887;194.575;195.364;199.223;193.038;198.399;193.172;198.058;204.413;215.048;218.997;219.004;212.925;207.849;200.566;180.895;196.677}

H5:        =”=COS(RADIANS(“&G3&”))*COS(RADIANS(“&G4&” + v_View_Az))”

=COS(RADIANS(C_Alaska_lat))*COS(RADIANS(C_Alaska_lon + v_View_Az))

H6:        =”=SIN(RADIANS(“&G3&”))”

=SIN(RADIANS(C_Alaska_lat))

The Formula in Columns G & H are setup to return a Text String, that replicates the formula required for the Name Manager.

The formula in Cells H3 and H4 use a User Defined Function, Concat(), to append the Latitude and Longitude values into a string with ; as a separator and appropriate ={ and } leading and trailing brackets.

I modified the Concat UDF to allow an optional Surrounding character ” as well as to skip over blank cells.

This change allowed Names to be stored as strings and also allowed large ranges to be uploaded at once

Eg:

={test1;test2;test3} is not permitted as a Named Formula formula unless test1, test 2 and test3 are existing Named Formula

={“test1”;”test2”;”test3”} is permitted, as test1 is a Text string.

H5 & H6 setup formula to return the X & Y values for each of the Points defining Alaska. Note that these formulas make use of the Named Formula in H3 and H4 above them. They also refer to a Named Formula v_View_Az. We haven’t set this up yet, but don’t worry about that yet. It simply contains the viewing azimuth of the view that the user wants to see the globe from, ie: How far to rotate the data so that it looks like the globe has turned.

The Concat() UDF

The use of Concat() UDF was described in a previous post at: Concat

Load Named Formula

Finally we need to load these new named formula into the Name Manager

We could do this manually, by selecting each range and ….

No, no no

In one of my previous posts I describe a technique to load named Formula from the worksheet. It is described in:  Automating Repetitive Tasks

The Automating Repetitive Tasks post above also describes the use of a small pieces of VBA Code which will allow these named formula to be uploaded quickly. This code is supplied included in the Hui’s World model.

For Alaska Select G3:G6

Execute a VBA Macro by using Alt+F8

 

Select aa_Load_Named_Ranges and press Ok. The aa_ prefix was simply added to the macro’s name so that it appears at the top of the Module List and is hence easier to find. So easy that you don’t need to select it, simply press Enter. This will be discussed in the section Formula Updates.

The 4 Named Formula selected were just created or updated if they previously existed.

Each Named Formula contains the formula comprised of the text to the right of it in Column H

Now just repeat this for the other Countries, Cities and other details.

Or setup all the countries and other data and upload them all in once step.

The aa_Load_Named_Ranges module skips blank lines, so you can setup a whole block of these adjacent to each set of data and then upload the entire lot by selecting say G1:G2000, then running the aa_Load_Named_Ranges module described above.


Adding Series to the Chart

Once the Named Formula for the Countries, Cities and Date Lines we can add a chart and upload each Country as a series

Select a single cell then got the Insert Tab and click on the Scatter Chart Icon,

Select any of the Scatter Charts as we will be formatting the Chart’s series later on.

Take note of the Charts Name

Each series requires a Name, a Range for X values and a Range for Y Values.

These are usually entered via the Add Series Dialog shown above.

However as we will require some x & y series to be added it is a lot easier to use some VBA to add the series.

For Alaska we have the Name: Alaska, with the X Series: C_Alaska_x and Y Series: C_Alaska_y

Again we can do this manually for each of the data sets or we can set this up and then use a small VBA snippet to add all the series to the chart in one go.

To achieve this I will use another code from the Automation post, Add_Cht_Series

To run this I setup 3 formulas for each series, then copied these down and relink the ranges for the other series

Using Alaska as the example

I10:           =”=”””&A3&””””

J10:           =”=Globe!”&G5

K10:          =“=Globe!”&G6

Setup a list of Country names, with adjacent x & Y values named Formula names

Note they are offset from columns G & H to avoid being uploaded as Named Formula