World Stock Market Gains and Losses

In this example, we read stock market information out of a database and visualize gains and losses using Mathematica's built-in WorldPlot package.

Implementation

Setup

Load Database Access Kit and the DataManipulation package.

<< DatabaseAccess`

<< Statistics`DataManipulation`

Open the database.

excelstock = OpenDataSource["stocks97"]

Query the table names.

DataSourceTables[excelstock]

Query the column names and data formats.

DataSourceColumns[excelstock, 
      TableName -> "stocks97"]

Get a list of all countries covered in the data set.

stockcountries = 
        Flatten[DataSourceEvaluate[excelstock,  
            SQLSelect[SQLTable["stocks97"], 
              SQLColumn["Country"]]]];

Clean up some names that are either out of date or lack spaces.

\(\(cleanup = {"ElSalvador" -> "El Salvador", "CzechRepublic" -> "Czech Republic", "NewZealand" -> "New Zealand", "DominicanRepublic" -> "Dominican Republic", "SaudiArabia" -> "Saudi Arabia", "SouthAfrica" -> "South Africa", "UnitedArabEmirates" -> "United Arab Emirates", "SriLanka" -> "Sri Lanka", "UnitedKingdom" -> "United Kingdom", "UnitedStates" -> "USA", "Yugoslavia" -> "Serbia and Montenegro"};\)\)

stockcountries = stockcountries /. cleanup;

Load the WorldPlot package to display the result graphically.

<< Miscellaneous`WorldPlot`

shadefunc[country_] :=
   If[MemberQ[stockcountries, country],RGBColor[1,0,0],GrayLevel[0.6] ]

The color function causes countries whose stock markets are in the
database to be colored red.

WorldPlot[{World, shadefunc}];

Selecting by Percentage Gain or Loss

Read the countries and the market gains and losses into Mathematica.

gainloss = 
        DataSourceEvaluate[excelstock,  
            SQLSelect[
              SQLTable["stocks97"], {SQLColumn["Country"], 
                SQLColumn["LocalIndexGain/Lossinlocal currency"]}]] /. 
          cleanup;

Shadefunc colors winners green, losers red, and markets with missing data blue.

shadefunc2[country_] := 
      If[MemberQ[Transpose[gainloss][[1]], country], 
        Which[
Select[
                gainloss, #[[1]] == country &][[1, 
                2]] == "NULL", 
          RGBColor[0, 0, 
            1], 
Select[
                gainloss, #[[1]] == country &][[1, 2]] >= 0, 
          RGBColor[0, 1, 
            0], 
Select[
                gainloss, #[[1]] == country &][[1, 2]] < 0, 
          RGBColor[1, 0, 0]], 
GrayLevel[0.6]]

WorldPlot[{World, shadefunc2}];

A more sophisticated shade function colors from bright red (high losses) to bright green (high gains). It's easy to see that for 1997 the highest gainer was Turkey. The biggest losses appeared in Romania.

First off, find the maximal gain and loss in order to scale the colors correctly.

maxgain = 
        Max[DropNonNumeric[Transpose[gainloss][[2]]]];

maxloss = 
        Min[DropNonNumeric[Transpose[gainloss][[2]]]];

shadefunc3[country_] := 
      If[MemberQ[Transpose[gainloss][[1]], country], 
        Which[
Select[
                gainloss, #[[1]] == country &][[1, 
                2]] == "NULL", 
          RGBColor[0, 0, 
            1], 
(temp = Select[
                    gainloss, #[[1]] == country &][[1, 
                    2]]) >= 0, 
          RGBColor[0, (1 + temp/maxgain)/2, 
            0], 
(temp2 = Select[
                    gainloss, #[[1]] == country &][[1, 
                    2]]) < 0, 
          RGBColor[(1 + temp2/maxloss)/2, 0, 
            0]], 
GrayLevel[0.6]]

WorldPlot[{World, shadefunc3}, 
        PlotLabel -> 
          StyleForm["Annual Gains and Losses", Section]];