World Stock Market Gains

Setup

<< DatabaseAccess`

excelstock = OpenDataSource["stocks97"]

DataSourceTables[excelstock]

DataSourceColumns[excelstock, 
      TableName -> "stocks97"]

World stock markets

Query which countries are 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 which stock markets are in the database to be colored red.

WorldPlot[{World, shadefunc}];

Finding winners and losers

The following command selects just the countries (rows) which stock markets posted gains in 1997.

stockgains = 
        Flatten[DataSourceEvaluate[excelstock,  
              SQLSelect[SQLTable["stocks97"], 
                SQLColumn[SQLTable["stocks97"], "Country"], 
                SQLColumn["LocalIndexGain/Lossinlocal currency"] > 0]]] /. 
          cleanup;

The following line colors the winners in green, the losers in red.

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

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.

shadefunc[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, shadefunc}];

A still 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. Interesting too that all Asia-Pacific lost in a similar range.

shadefunc[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/Max[
                      DropNonNumeric[Transpose[gainloss][[2]]]])/2, 
            0], 
(temp2 = Select[
                    gainloss, #[[1]] == country &][[1, 
                    2]]) < 0, 
          RGBColor[(1 + 
                  temp/Min[
                      DropNonNumeric[Transpose[gainloss][[2]]]])/2, 
            0, 0]], 
GrayLevel[0.6]]

WorldPlot[{World, shadefunc}];