DEV Community: Wynn Tee

How to create bins with a monotonic weight of evidence trend in Excel

Wynn Tee — Wed, 23 Feb 2022 11:51:37 +0000

Risk practitioners often apply weight of evidence (WOE) transformation to the independent variables of a logistic regression. A key challenge in the transformation is grouping data into bins that, when sorted logically, show a monotonic WOE trend.

QRS.BIN functions

QRS Toolbox for Excel includes functions for binning data with application to WOE transformation. The names of the functions all begin with QRS.BIN.

QRS.BIN.EQFREQ, QRS.BIN.EQWIDTH, and QRS.BIN.UNIQUE return bins using the equal frequency, equal width, and unique value methods respectively.

QRS.BIN.MONO returns bins using a novel combination of the Monotone Adjacent Pooling Algorithm and ChiMerge. It can be used to automatically create bins with a monotonic WOE trend.

QRS.BIN.MANUAL returns bins using user-defined criteria. It can be used to manually create bins with a monotonic WOE trend, such as by merging bins returned from one of the other QRS.BIN functions.

QRS.BIN.MAP returns mapped bin values. It can be used to map original data values to WOE values for subsequent use in QRS.LOGISTIC.REGRESSION.

QRS.BIN.MONO

To try QRS.BIN.MONO yourself, first add QRS Toolbox to your instance of Excel and start your free trial of QRS.BIN.MONO. Then, download and open the example workbook.

The data in the workbook is based on the 2020 version of the South German credit dataset. In the workbook:

Cells A2–A1001 contain credit duration in months for 1000 debtors.
Cells B2–B1001 contain ones if credit defaults occurred and zeros otherwise.

=QRS.BIN.MONO(A2:A1001, B2:B1001, 1, 7)

To group credit duration into 7 bins with a monotonic WOE trend, enter the formula =QRS.BIN.MONO(A2:A1001, B2:B1001, 1, 7) in cell E1. The third argument is 1, because credit risk generally increases as credit duration increases.

The result is a frequency table with 7 rows and 7 columns. The rows correspond to bins. The columns correspond to identifier, description, total frequency, frequency of zeros, frequency of ones, proportion of ones, and WOE respectively.

The WOE values in the final column increase monotonically, as required. Note that some applications assume a definition of WOE that differs by a negative sign.

=QRS.BIN.MONO(A2:A1001, B2:B1001, 1, 7, "HEADER", TRUE)

To improve the presentation of the result, add "HEADER", TRUE to the formula. The result now contains a header row.

The identifiers in the first column represent the lower bounds of the bins. They can be used as binning keys in QRS.BIN.MAP.

=QRS.BIN.MAP(A2:A1001, E2:E9, K2:K9)

Finally, to map the original credit duration values to WOE values, enter the formula =QRS.BIN.MAP(A2:A1001, E2:E9, K2:K9) in cell C2.

Please read the documentation to learn more about QRS.BIN.MONO, including how to include a total row and how to include only selected columns.

QRS.BIN.MANUAL

The bins created using QRS.BIN.MONO will always have a monotonic WOE trend, but they might not have "nice" lower bounds or "smooth" frequencies. For example, the lower bounds might not be integers, or the frequencies might not be unimodal.

Continuing with the example from the previous section, suppose that the bins are required to have not only a monotonic WOE trend, but also lower bounds that are multiples of 6 or 12 months.

=QRS.BIN.MANUAL(A2:A1001, B2:B1001, F15:F23, , "HEADER", TRUE, "EXC", TRUE, "COLS", "NO,DESC,N,WOE")

To create bins with such "nice" lower bounds, enter 0, 6, 12, 18, 24, 30, 36, 48, 60 in cells F15–F23, and enter the formula =QRS.BIN.MANUAL(A2:A1001, B2:B1001, F15:F23, , "HEADER", TRUE, "EXC", TRUE, "COLS", "NO,DESC,N,WOE") in cell H14. The "EXC", TRUE option turns the lower bounds into exclusive, rather than inclusive, bounds.

The result contains 9 bins that satisfy the "nice" lower bounds requirement, but not the monotonic WOE trend requirement. The WOE of the ninth bin cannot even be calculated. A possible solution is to merge some of the bins.

=QRS.BIN.MANUAL(A2:A1001, B2:B1001, F15:F23, E15:E23, "HEADER", TRUE, "EXC", TRUE, "COLS", "NO,DESC,N,WOE")

To merge bins 3–4, 5–6, and 7–9, enter the new bin numbers 1, 2, 3, 3, 4, 4, 5, 5, 5 in cells E15–E23, and change the empty fourth argument in cell H14 to E15:E23.

The result now contains 5 bins that satisfy both the "nice" lower bounds and monotonic WOE trend requirements.

=QRS.BIN.MAP(E15:E23, H15:H19, K15:K19)

Finally, to map the original credit duration values to WOE values, enter the formula =QRS.BIN.MAP(E15:E23, H15:H19, K15:K19) in cell G15.

=QRS.BIN.MAP(A2:A1001, F15:F23, G15:G23)

Then, enter the formula =QRS.BIN.MAP(A2:A1001, F15:F23, G15:G23) in cell C2. The mapping is done in two steps, because the original values and WOE values are indirectly linked by the bin numbers.

Please read the documentation to learn more about QRS.BIN.MANUAL, including how to use regular expressions for text values and how to handle stray values.

Final remarks

If you would like to use QRS.BIN.MONO beyond your free trial period, you may purchase the right to use it indefinitely for as little as USD 9.00. If you find the QRS.BIN functions useful, please share this page with other potential users.

How to calculate accuracy ratio in Excel using only a formula

Wynn Tee — Mon, 15 Nov 2021 13:48:36 +0000

Risk practitioners often use accuracy ratio (AR) to measure the discriminatory power of binary classification models, such as models of credit default and insurance fraud. The closer AR is to 1, the higher the discriminatory power of the model.

Definition

The above diagram shows the cumulative accuracy profiles of a realistic model, a random model, and a perfect model.

As the proportion of observations increases, a perfect model would correctly classify all events before all non-events, but a random model would indiscriminately classify events and non-events together. A realistic model would be somewhere in between.

AR is the ratio of the area between the cumulative accuracy profiles of the realistic and random models (B) to the area between the cumulative accuracy profiles of the perfect and random models (A+B).

Unfortunately, Excel does not come with a native function for calculating AR. It is possible to calculate AR in Excel manually, but the process involves auxiliary rows and columns with complicated formulas that have to be adjusted as observations are added or removed. The problem is exacerbated with the standard error of AR.

QRS.DISC.AR

Fortunately, QRS Toolbox for Excel includes the QRS.DISC.AR function for calculating AR. It is applicable to both grouped and ungrouped data.

To try QRS.DISC.AR yourself, first add QRS Toolbox to your instance of Excel and then open the example workbook.

The workbook contains 2 worksheets. In the UNGROUPED worksheet:

Cells A2–A2001 contain credit scores for 2000 borrowers. The scores range between 0 for least creditworthy and 100 for most creditworthy.
Cells B2–B2001 contain ones if credit default occurred and zeros otherwise.

In the GROUPED worksheet, cells A2–E8 contain data from the previous worksheet grouped into 7 score ranges, each with an alphabetical rating grade.

Ungrouped data example

=QRS.DISC.AR(A2:A2001, B2:B2001)

To calculate AR of the ungrouped data, open the UNGROUPED worksheet and enter the formula =QRS.DISC.AR(A2:A2001, B2:B2001) in cell D1. The result is -0.794, which is generally considered to be a large AR in absolute terms.

The result is negative-valued, because the credit scores and credit default events in this example are negatively correlated by design. In a perfect model, score=0 corresponds to event=1, and score=100 corresponds to event=0.

Significance test

=QRS.DISC.AR(A2:A2001, B2:B2001, "TEST", "RAG")

To determine the statistical significance of the AR, add "TEST", "RAG" to the formula. The result now contains a second row with a red/amber/green rating that summarizes the significance test.

The AR in this example has a green rating. A green/amber rating means the AR is significant at the 5%/10% significance level. A red rating means the AR is not significant at the 10% significance level.

Please read the documentation to learn how to return the p-value and other useful information about the significance test, as well as how to change the significance levels of the ratings.

Labels and transpose

=QRS.DISC.AR(A2:A2001, B2:B2001, "TEST", "RAG", "LABELS", TRUE)

To add labels to the result, add "LABELS", TRUE to the formula. To swap the rows and columns of the result, add "TRANSPOSE", TRUE to the formula.

Grouped data example

=QRS.DISC.AR(B2:B8, D2:E8)

To calculate AR of the grouped data, switch to the GROUPED worksheet and enter the formula =QRS.DISC.AR(B2:B8, D2:E8) in cell F9. The result is -0.797, which is similar to the AR of the ungrouped data.

The TEST, LABELS, and TRANSPOSE options can be used as before.

Final remarks

If you find QRS.DISC.AR useful, please share this page with other potential users.

How to estimate value-at-risk given skewness and kurtosis in Excel

Wynn Tee — Mon, 15 Nov 2021 09:01:20 +0000

Risk practitioners often use value-at-risk (VaR) to quantify the potential future loss of an investment. As a risk metric, VaR answers questions like "What is the worst daily/monthly/yearly return going to be 90%/95%/99% of the time?"

Terminology

In statistical jargon, VaR is simply the percentile of the return distribution at a given confidence level. The above diagram illustrates VaR at the 95% confidence level.

Sometimes, VaR is defined in terms of a loss (instead of return) distribution. In that case, the above diagram would flip horizontally and VaR would be in the right tail.

Mean and variance

If the mean and variance of the return distribution are given, VaR can be estimated by assuming returns follow the normal distribution and calculating the percentile of the normal distribution that fits the given mean and variance.

To estimate VaR at the 95% confidence level in Excel when the mean and variance of the return distribution are 10% and 0.01 respectively, enter the formula =NORM.INV(1-0.95, 10%, SQRT(0.01)) in a cell.

To estimate VaR when the previous mean and variance are for the loss (instead of return) distribution, change the formula to =NORM.INV(0.95, 10%, SQRT(0.01)).

Skewness and kurtosis

In reality, returns are more likely to follow a non-normal distribution with negative skewness and large kurtosis. According to journalist Martin Wolf, many investment strategies have a high probability of small gains and a low probability of huge losses.

If the skewness and kurtosis of the return distribution are given in addition to the mean and variance, VaR can be estimated by assuming returns follow a non-normal parametric distribution and calculating the percentile of the distribution that fits the given moments.

There are many non-normal parametric distributions, but the Pearson and Johnson distributions are the only ones that fit all possible combinations of skewness and kurtosis. No other parametric distribution has such a feature.

Unfortunately, Excel does not come with native functions for using the Pearson and Johnson distributions.

QRS.VAR functions

Fortunately, QRS Toolbox for Excel includes functions for estimating VaR from not only the normal distribution, but also the Pearson and Johnson distributions. The names of the functions all begin with QRS.VAR.

QRS.VAR.NORMAL returns VaR for the normal distribution. It accepts the confidence level, mean, standard deviation, and choice of tail as parameters. It is simpler to use compared to the two previous formulas involving NORM.INV.

QRS.VAR.PEARSON and QRS.VAR.JOHNSON return VaR for the Pearson and Johnson distributions respectively. They accept the same parameters as QRS.VAR.NORMAL in addition to skewness and kurtosis.

Pearson Type IV example

There are 5 main types of Pearson distributions, namely Types I, III, IV, V, and VI. To the best of our knowledge, QRS.VAR.PEARSON is the only publicly available Excel function for estimating VaR from all 5 types of Pearson distributions.

The Pearson Type IV distribution deserves special mention. It is a natural alternative to the normal distribution in risk applications, because it is not only unimodal and unbounded, but also skewed (squared skewness < 32) and fat-tailed (kurtosis > 3).

To try QRS.VAR.PEARSON yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.VAR.PEARSON. Then, open the example workbook.

In the workbook:

Cell B1 contains the confidence level, 99%.
Cell B2 contains the mean, 10%.
Cell B3 contains the variance, 0.01.
Cell B4 contains the skewness, -2.
Cell B5 contains the kurtosis, 16.

=QRS.PEARSON.TYPE(B2, B3, B4, B5)

To determine the type of Pearson distribution that fits the given moments, enter the formula =QRS.PEARSON.TYPE(B2, B3, B4, B5) in cell B6. The result is IV.

=QRS.VAR.PEARSON(B1, B2, B3, B4, B5)

To estimate VaR for the corresponding Pearson Type IV distribution, enter the formula =QRS.VAR.PEARSON(B1, B2, B3, B4, B5) in cell B7. The result is -23.7%, which means the investment is estimated to lose at most 23.7% in 99 out of 100 periods.

Johnson SU example

There are 3 types of Johnson distributions, namely SU, SL, and SB. To the best of our knowledge, QRS.VAR.JOHNSON is the only publicly available Excel function for estimating VaR from all 3 types of Johnson distributions by moment matching.

Software that support fitting the Johnson distributions by moments typically use a method by Hill, Hill, and Holder, but that method does not always return a result. QRS.VAR.JOHNSON uses a proprietary method that is more reliable.

To try QRS.VAR.JOHNSON yourself, start your free trial of QRS.VAR.JOHNSON and reopen the workbook from the previous example.

=QRS.JOHNSON.TYPE(B2, B3, B4, B5)

To determine the type of Johnson distribution that fits the given moments, enter the formula =QRS.JOHNSON.TYPE(B2, B3, B4, B5) in cell B6. The result is SU.

=QRS.VAR.JOHNSON(B1, B2, B3, B4, B5)

To estimate VaR for the corresponding Johnson SU distribution, enter the formula =QRS.VAR.JOHNSON(B1, B2, B3, B4, B5) in cell B7. The result is -24.3%, which is in the same ballpark as the result from the previous example.

Final remarks

If you would like to use QRS.VAR.PEARSON and QRS.VAR.JOHNSON beyond your free trial periods, you may purchase the right to use them indefinitely for as little as USD 19.00. If you find the QRS.VAR functions useful, please share this page with other potential users.

How to estimate expected shortfall given skewness and kurtosis in Excel

Wynn Tee — Mon, 15 Nov 2021 05:24:56 +0000

Risk practitioners sometimes use expected shortfall (ES) to quantify the potential future loss of an investment. As a risk metric, ES answers questions like "What is the average daily/monthly/yearly return going to be in the worst 1%/5%/10% of times?"

Terminology

In statistical jargon, ES is the conditional mean of the return distribution given that returns are less than the percentile at a given confidence level. The above diagram illustrates ES at the 95% confidence level.

Sometimes, ES is defined in terms of a loss (instead of return) distribution. In that case, the above diagram would flip horizontally and ES would be in the right tail.

Mean and variance

If the mean and variance of the return distribution are given, ES can be estimated by assuming returns follow the normal distribution and calculating the conditional mean of the normal distribution that fits the given mean and variance.

To estimate ES at the 95% confidence level in Excel when the mean and variance of the return distribution are 10% and 0.01 respectively, enter the formula =10% - SQRT(0.01) * NORM.S.DIST(NORM.S.INV(1-0.95), FALSE) / (1-0.95) in a cell.

To estimate ES when the previous mean and variance are for the loss (instead of return) distribution, change the formula to =10% + SQRT(0.01) * NORM.S.DIST(NORM.S.INV(0.95), FALSE) / (1-0.95).

Skewness and kurtosis

If the skewness and kurtosis of the return distribution are given in addition to the mean and variance, ES can be estimated by assuming returns follow a non-normal parametric distribution and calculating the conditional mean of the distribution that fits the given moments.

Unfortunately, Excel does not come with native functions for using the Pearson and Johnson distributions.

QRS.ES functions

Fortunately, QRS Toolbox for Excel includes functions for estimating ES from not only the normal distribution, but also the Pearson and Johnson distributions. The names of the functions all begin with QRS.ES.

QRS.ES.NORMAL returns ES for the normal distribution. It accepts the confidence level, mean, standard deviation, and choice of tail as parameters. It is simpler to use compared to the two previous formulas involving NORM.S.DIST and NORM.S.INV.

QRS.ES.PEARSON and QRS.ES.JOHNSON return ES for the Pearson and Johnson distributions respectively. They accept the same parameters as QRS.ES.NORMAL in addition to skewness and kurtosis.

Pearson Type IV example

There are 5 main types of Pearson distributions, namely Types I, III, IV, V, and VI. To the best of our knowledge, QRS.ES.PEARSON is the only publicly available Excel function for estimating ES from all 5 types of Pearson distributions.

To try QRS.ES.PEARSON yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.ES.PEARSON. Then, open the example workbook.

In the workbook:

Cell B1 contains the confidence level, 99%.
Cell B2 contains the mean, 10%.
Cell B3 contains the variance, 0.01.
Cell B4 contains the skewness, -2.
Cell B5 contains the kurtosis, 16.

=QRS.PEARSON.TYPE(B2, B3, B4, B5)

To determine the type of Pearson distribution that fits the given moments, enter the formula =QRS.PEARSON.TYPE(B2, B3, B4, B5) in cell B6. The result is IV.

=QRS.ES.PEARSON(B1, B2, B3, B4, B5)

To estimate ES for the corresponding Pearson Type IV distribution, enter the formula =QRS.ES.PEARSON(B1, B2, B3, B4, B5) in cell B7. The result is -36.7%, which means the investment is estimated to lose on average 36.7% in 1 out of 100 periods.

Johnson SU example

There are 3 types of Johnson distributions, namely SU, SL, and SB. To the best of our knowledge, QRS.ES.JOHNSON is the only publicly available Excel function for estimating ES from all 3 types of Johnson distributions by moment matching.

Software that support fitting the Johnson distributions by moments typically use a method by Hill, Hill, and Holder, but that method does not always return a result. QRS.ES.JOHNSON uses a proprietary method that is more reliable.

To try QRS.ES.JOHNSON yourself, start your free trial of QRS.ES.JOHNSON and reopen the workbook from the previous example.

=QRS.JOHNSON.TYPE(B2, B3, B4, B5)

To determine the type of Johnson distribution that fits the given moments, enter the formula =QRS.JOHNSON.TYPE(B2, B3, B4, B5) in cell B6. The result is SU.

=QRS.ES.JOHNSON(B1, B2, B3, B4, B5)

To estimate ES for the corresponding Johnson SU distribution, enter the formula =QRS.ES.JOHNSON(B1, B2, B3, B4, B5) in cell B7. The result is -38.1%, which is in the same ballpark as the result from the previous example.

Final remarks

If you would like to use QRS.ES.PEARSON and QRS.ES.JOHNSON beyond your free trial periods, you may purchase the right to use them indefinitely for as little as USD 19.00. If you find the QRS.ES functions useful, please share this page with other potential users.

How to perform logistic regression in Excel using only a formula

Wynn Tee — Mon, 08 Nov 2021 01:19:23 +0000

Logistic regression is a process for modelling the probability of a binary outcome in terms of explanatory factors using a logistic function. It can be used to model the probability of a risk event occurring, such as credit default and insurance fraud.

QRS.LOGISTIC.REGRESSION

QRS Toolbox for Excel includes the QRS.LOGISTIC.REGRESSION function for performing logistic regression using nothing more than a formula. The function includes options to return the same results as more expensive commercial products.

To try QRS.LOGISTIC.REGRESSION yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.LOGISTIC.REGRESSION. Then, download and open the example workbook.

In the workbook:

Cells A7–A33 contain identifiers for 27 leukemia patients.
Cells B7–B33 contain ones if remission occurred and zeros otherwise.
Cells C7–H33 contain factors that potentially explain the occurrence of remission.
Cells C6–H6 contain shortened names of the factors.

Constant and coefficients

=QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33)

To perform a logistic regression between the occurrence of remission and the given factors, enter the formula =QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33) in cell A1. The result contains 7 numbers. The first number is the regression constant. The remaining 6 numbers are the coefficients of the factors.

Labels and headers

=QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33, "LABELS", TRUE, "NAMES", C6:H6)

To improve the presentation of the result, add "LABELS", TRUE and "NAMES", C6:H6 to the formula. The result now contains row labels and column headers.

Significance tests

=QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33, "LABELS", TRUE, "NAMES", C6:H6, "LRTEST", "RAG")

To determine the statistical significance of the factors, add "LRTEST", "RAG" to the formula. The result now contains red/amber/green ratings that summarize the likelihood ratio test for each factor.

The LI factor has a green rating. The other factors have red ratings. A green/amber rating means a factor is significant at the 5%/10% significance level. A red rating means a factor is not significant at the 10% significance level.

Please read the documentation to learn how to return the test statistic and p-value of the likelihood ratio test, as well as the corresponding results of the Wald test.

Significance levels

=QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33, "LABELS", TRUE, "NAMES", C6:H6, "LRTEST", "RAG", "PGREEN", 0.3, "PRED", 0.35)

To change the significance levels from the default values of 5% and 10% to, say, 30% and 35%, add "PGREEN", 0.3 and "PRED", 0.35 to the formula. The TEMP factor now has a green rating too.

Manual factor selection

=QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33, "LABELS", TRUE, "NAMES", C6:H6, "LRTEST", "RAG", "PGREEN", 0.3, "PRED", 0.35, "MASK", C5:H5)

To manually select only the LI and TEMP factors, enter 0, 0, 0, 1, 0, 1 in cells C5–H5 and add "MASK", C5:H5 to the formula. The excluded factors now have coefficients equal to zero.

Automatic factor selection

=QRS.LOGISTIC.REGRESSION(C7:H33, B7:B33, "LABELS", TRUE, "NAMES", C6:H6, "LRTEST", "RAG", "PGREEN", 0.3, "PRED", 0.35, "METHOD", "STEPWISE")

To automatically select factors using stepwise selection, remove "MASK", C5:H5 and add "METHOD", "STEPWISE" to the formula. The automatically selected factors are CELL, LI, and TEMP.

Please read the documentation to learn how to use forward selection or backward elimination instead, and how to control the significance levels for factor selection.

QRS.LOGISTIC.MODEL

You can calculate the probability modelled by a logistic regression in Excel using the QRS.LOGISTIC.MODEL function.

=QRS.LOGISTIC.MODEL(B$2:H$2, C7:H7)

Continuing from the example above, to calculate the probability of remission, enter the formula =QRS.LOGISTIC.MODEL(B$2:H$2, C7:H7) in cell I7, and copy the formula across cells I8–I33.

The probability of remission for Patient 01 is 72%. The probability for Patient 02 is 58%. The probability for Patient 03 is 10%, and so on.

Final remarks

If you find QRS.LOGISTIC.REGRESSION useful and would like to use it beyond your free trial period, you may purchase the right to use it indefinitely for as little as USD 29.00.

How to seamlessly exchange data between JavaScript environments

Wynn Tee — Thu, 04 Nov 2021 02:22:55 +0000

JSON limitations

Wouldn't you find it strange if adults who are fluent in the same language spoke to each other using the vocabulary of a 3-year-old? Well, something analogous is happening when browsers and JavaScript servers exchange data using JSON, the de facto serialization format on the internet.

For example, if we wanted to send a Date object from a JavaScript server to a browser, we would have to:

Convert the Date object to a number.
Convert the number to a JSON string.
Send the JSON string to the browser.
Revert the JSON string to a number.
Realize the number represents a date.
Revert the number to a Date object.

This roundabout route seems ludicrous, because the browser and server both support the Date object, but is necessary, because JSON does not support the Date object.

In fact, JSON does not support most of the data types and data structures intrinsic to JavaScript.

JOSS as a solution

The aforementioned limitations of JSON motivated us to create the JS Open Serialization Scheme (JOSS), a new binary serialization format that supports almost all data types and data structures intrinsic to JavaScript.

JOSS also supports some often overlooked features of JavaScript, such as primitive wrapper objects, circular references, sparse arrays, and negative zeros. Please read the official specification for all the gory details.

JOSS serializations come with the textbook advantages that binary formats have over text formats, such as efficient storage of numeric data and ability to be consumed as streams. The latter allows for JOSS serializations to be handled asynchronously, which we shall see in the next section.

Reference implementation

The reference implementation of JOSS is available to be downloaded as an ES module (for browsers and Deno), CommonJS module (for Node.js), and IIFE (for older browsers). It provides the following methods:

serialize() and deserialize() to handle serializations in the form of static data.
serializable(), deserializable(), and deserializing() to handle serializations in the form of readable streams.

To illustrate the syntax of the methods, allow us to guide you through an example in Node.js.

First, we import the CommonJS module into a variable called JOSS.

// Change the path accordingly
const JOSS = require("/path/to/joss.node.min.js");

Next, we create some dummy data.

const data = {
  simples: [null, undefined, true, false],
  numbers: [0, -0, Math.PI, Infinity, -Infinity, NaN],
  strings: ["", "Hello world", "I \u2661 JavaScript"],
  bigints: [72057594037927935n, 1152921504606846975n],
  sparse: ["a", , , , , ,"g"],
  object: {foo: {bar: "baz"}},
  map: new Map([[new String("foo"), new String("bar")]]),
  set: new Set([new Number(123), new Number(456)]),
  date: new Date(),
  regexp: /ab+c/gi,
};

To serialize the data, we use the JOSS.serialize() method, which returns the serialized bytes as a Uint8Array or Buffer object.

const bytes = JOSS.serialize(data);

To deserialize, we use the JOSS.deserialize() method, which simply returns the deserialized data.

const copy = JOSS.deserialize(bytes);

If we inspect the original data and deserialized data, we will find they look exactly the same.

console.log(data, copy);

It should be evident by now that you can migrate from JSON to JOSS by replacing all occurrences of JSON.stringify/parse in your code with JOSS.serialize/deserialize.

Readable Streams

If the data to be serialized is large, it is better to work with readable streams to avoid blocking the JavaScript event loop.

To serialize the data, we use the JOSS.serializable() method, which returns a readable stream from which the serialized bytes can be read.

const readable = JOSS.serializable(data);

To deserialize, we use the JOSS.deserializable() method, which returns a writable stream to which the readable stream can be piped.

const writable = JOSS.deserializable();
readable.pipe(writable).on("finish", () => {
  const copy = writable.result;
  console.log(data, copy);
});

To access the deserialized data, we wait for the piping process to complete and read the result property of the writable stream.

Whilst writable streams are well supported in Deno and Node.js, they are either not supported or not enabled by default in browsers at the present time.

To deserialize when we do not have recourse to writable streams, we use the JOSS.deserializing() method, which returns a Promise that resolves to the deserialized data.

const readable2 = JOSS.serializable(data);
const promise = JOSS.deserializing(readable2);
promise.then((result) => {
  const copy = result;
  console.log(data, copy);
});

Servers

In practice, we would serialize data to be sent in an outgoing HTTP request or response, and deserialize data received from an incoming HTTP request or response.

The reference implementation page contains examples on how to use JOSS in the context of the Fetch API, Deno HTTP server, and Node.js HTTP server.

Closing remarks

JOSS will evolve with the JavaScript specification. To keep track of changes to JOSS, please star or watch the GitHub repository.

How to generate random numbers for Monte Carlo simulation in Excel

Wynn Tee — Sun, 31 Oct 2021 03:41:23 +0000

Risk practitioners often use Monte Carlo simulation to simulate the potential future outcomes of a decision. A key requirement for Monte Carlo simulation is the ability to generate random numbers from a desired probability distribution.

Analysis Toolpak

Excel comes with an add-in called Analysis Toolpak, which includes a graphical user interface for generating random numbers from the uniform distribution, normal distribution, and several discrete probability distributions.

However, the graphical user interface can be inconvenient when you want to change the parameters or regenerate the random numbers.

RANDARRAY function

Excel also comes with the RAND and RANDARRAY functions, which return uniformly distributed random numbers. The functions can be used with inverse sampling to generate random numbers from any probability distribution whose inverse cumulative distribution function can be evaluated in Excel.

For example, to generate a column of 100,000 random numbers from the standard normal distribution, enter the formula =NORM.S.INV(RANDARRAY(100000,1)) in a cell.

However, this method can be inefficient when you want to generate a lot of random numbers and the inverse cumulative distribution function is expensive to evaluate.

QRS.RAND functions

QRS Toolbox for Excel includes functions for generating random numbers from some probability distributions using rejection sampling. The names of all the functions begin with QRS.RAND.

QRS.RAND.NORMAL and QRS.RAND.EXP return random numbers from the normal and exponential distributions respectively using the ziggurat algorithm.

QRS.RAND.GAMMA returns random numbers from the gamma distribution. It can be used to generate random numbers from gamma-related distributions, such as the chi-squared distribution and Beta distribution.

QRS.RAND.PEARSON and QRS.RAND.JOHNSON return random numbers from the Pearson and Johnson distributions respectively. They can be used to simulate rare but extreme events, because the Pearson and Johnson distributions uniquely fit all possible combinations of skewness and kurtosis.

Pearson Type IV example

There are in fact 5 types of Pearson distributions, namely Types I, III, IV, V, and VI. To the best of our knowledge, QRS.RAND.PEARSON is the only publicly available Excel function for generating random numbers from all 5 types of Pearson distributions.

To try QRS.RAND.PEARSON yourself, add QRS Toolbox to your instance of Excel and start your free trial of QRS.RAND.PEARSON. Then, open a blank workbook.

=QRS.RAND.PEARSON(100000, 1, 0, 1, 2, 16)

To generate a column of 100,000 random numbers from the Pearson Type IV distribution with mean 0, variance 1, skewness 2, and kurtosis 16, enter the formula =QRS.RAND.PEARSON(100000, 1, 0, 1, 2, 16) in cell A1. The random numbers appear in cells A1:A100000.

=QRS.RAND.PEARSON(100000, 5, 0, 1, 2, 16)

To generate 5 columns of random numbers instead, change the second argument to 5 so that the formula becomes =QRS.RAND.PEARSON(100000, 5, 0, 1, 2, 16). The random numbers now appear in cells A1:E100000.

To regenerate the random numbers, simply recalculate cell A1. Unlike the RAND and RANDARRAY functions, the QRS.RAND functions are designed to be non-volatile.

Final remarks

The other QRS.RAND functions can be used in a similar manner. If you find the QRS.RAND functions useful, please share this page with other potential users.