What data is on the menu?

What data is on the menu?

We will use economic data in this tutorial, since there are easily accessible and publicly available economic databases. But Ravel can handle data on any topic, from marketing to palaeontology. Here are four examples.

Business

Businesses need to know the characteristics of their customers, which marketing methods are working best, how good profit margins are, which salespeople are performing best, and so on. Many small businesses collect transactional data using MYOB and similar programs. But they often don’t analyse it, because it’s just too complicated to do that with spreadsheets, and they can’t afford to hire a dedicated data analyst who can use BI programs for them. Figure 3 shows a sample data file from a real business with anonymized records.

Figure 3: Sales data for a small business

Figure 4 shows this data loaded into Ravel Salesperson, Source, and Suburb—become dimensions in Ravel , while there are 3 pieces of information—Quote, Discount, and Price—which are aggregated onto a 5th dimension named “Data”.

Several slices of data are made for further analysis: each of the locked variables next to the Ravel is equivalent to a Pivot Table in Business Intelligence programs like Power BI and Tableau .

Figure 4: Analyzing sales data with Ravel

other slices can be analysed to work out the best performing suburbs, trends over time, etc. And all the while, the formulas doing the analysis are easily to read, understand, and audit.

Science

There are numerous studies that attempt to measure the temperature of the Earth’s atmosphere over time. Figure 5 shows four such estimates, ranging from average annual temperature measurements from 1880 onwards,[1] estimates of global average temperatures for every year from 0AD till 2000,[2] a reconstruction of temperature every thousand years back to 2 million years BCE,[3] and estimates of temperature every million years back to 540 million years ago.[4] The first three series show deviations from a benchmark average, while the final series shows estimated surface air temperatures in degrees Celsius.

Figure 5: Four data sets of global average temperature giving data by years, thousand years, and millions of years

The formula shown in Figure 6 collates these into one data series from 540 million years ago to 2023, with time steps ranging from millions of years to single years.

This enables plots ranging from all years from 0 to 2023 (the top plot in Figure 7), to every million years since the extinction of the dinosaurs (the last plot), to be easily generated from the collated data.

civilisation, since the oldest known monolithic buildings were built roughly 12,000 years ago in modern-day Turkey, occurs in one brief warm spell at the top of a 6°-degree “heat hill”, in the middle of a highly unusual period of roughly 100,000-year glaciation cycles over the last 1.5 million years.

Figure 7: Human civilisation has existed for a brief and highly unusual period in the Earth’s climate history

Economics

The Bank of International Settlements provides many free databases, on banking, debt, house prices, consumer prices, and interest rates (see https://data.bis.org/). One piece of information the BIS lacks though is GDP. However, since there is data on debt in each country’s domestic currency (and also in US dollar terms), and debt as a percentage of GDP, it is possible to derive GDP data—in both domestic currencies and the US dollar—by a simple pair of formulas. If you divide debt in domestic currencies by the debt to GDP ratio in percentage terms, and then multiply the result by 100 (to compensate for the fact that, by using percentages, you’re dividing by 100 times the debt ratio), your formula gives you GDP in domestic currency terms. The same trick also works for debt in US dollar terms—see Figure 8.

The same calculations could be done in a spreadsheet, but the relevant cell reference formulas would need to be replicated tens of thousands of times—once for each combination of the 43 countries and 333 quarters in the BIS database. Figure 9 shows this formula in Ravel, and its use to find the ten biggest economies in the world in 2000 and 2023.

Figure 9: Deriving GDP in US$ for the 43 countries in the BIS Database

Finance

asset and its moving average to work out whether to buy, sell or hold the asset.

Ravel can calculate moving averages by dividing a running sum number of data points to sum—seven in the example shown in Figure 10—by a running sum of the number 1 with the same number of specified data points.

Figure 10: The argument in Running Sum tells Ravel the number of dates over which to sum

A simple way to generate a vector of 1s with the same length as the data is to divide the data by itself—as shown in Figure 11, where the variable Daily is divided by itself. The running sum is then done over 7 days, which generates a vector with the numbers 1 to 6 in its first 6 entries, followed by the number 7, to generate a seven-day moving average. The same process generates vectors with 1 to 29 in the first 29 entries, followed by 30s for the 30-day MA, and 1 to 249 in the first 249 entries, followed by 250 for the 250-day MA. These are then used to divide the summed Closing value—shown here as the variable Daily over the same time periods.

Figure 11: Calculating 7-, 30- and 250-day moving averages with Ravel

The caliper shown on the Ravel in Figure 11 can be used to reduce this data window, or slide it to a different time period, as illustrated by Figure 12.

Figure 12: Sliding the caliper to show the beginning of the data series, which includes the 1929 Wall Street Crash

There are many free databases of economics information, and in the remainder of this tutorial guide, we’ll mainly use economic data to demonstrate how to use Ravel . But, as these examples show, Ravel can analyse any type of data, more easily, powerfully, and transparently, than can be done with a spreadsheet or BI program, or via a data-oriented programming language.

Footnotes

1 Lenssen, N., G. Schmidt, J. Hansen, M. Menne, A. Persin, R. Ruedy and D. Zyss (2019). "Improvements in the GISTEMP uncertainty model." J. Geophys. Res. Atmos 24: 6307-6326, and Team, G. (2024). GISS Surface Temperature Analysis (GISTEMP), version 4. NASA Goddard Institute for Space Studies. See https://data.giss.nasa.gov/gistemp/

2 Neukom, R., L. A. Barboza, M. P. Erb, F. Shi, J. Emile-Geay, M. N. Evans, J. Franke, D. S. Kaufman, L. Lücke, K. Rehfeld, A. Schurer, F. Zhu, S. Brönnimann, G. J. Hakim, B. J. Henley, F. C. Ljungqvist, N. McKay, V. Valler, L. von Gunten and P. k. Consortium (2019). "Consistent multidecadal variability in global temperature reconstructions and simulations over the Common Era." Nature Geoscience 12(8): 643-649. See https://doi.org/10.1038/s41561-019-0400-0.

3 Snyder, C. W. (2016). "Evolution of global temperature over the past two million years." Nature 538(7624): 226-228. See https://doi.org/10.1038/nature19798

4 Scotese, C. R., H. Song, B. J. W. Mills and D. G. Van Der Meer (2021). "Phanerozoic paleotemperatures: The earth’s changing climate during the last 540 million years." Earth-Science Reviews 215: 103503. See https://dx.doi.org/10.1016/j.earscirev.2021.103503