Excel: Converting String Date to Date Type

This post demonstrates how to convert a string-formatted date to a Date type using three Excel formulas.

Excel : Split the given string by underscores and select the n-th element

This post shows how to split the given string by the underscores and select the nth element using Excel functions.

Excel : the first and last day of month with and without weekends or holidays

This post shows how to determine the first or last day of a month taking weekend or holidays into account using Excel functions with a user-defined table of holidays. This can be applied when generating a schedule of IRS swap cash flows.

Excel : Scan Numeric Data From a Time Series Plot Image

This post introduces a very nice scanning excel tool for scanning numerical values approximately from a plot image. We sometimes encounter the case when some academic papers show a figure plotting data which is hard to find. In this case, I think this might be useful.

Simpler and More Convenient Linear Interpolation in Excel

This post presents a simpler and more convenient Excel formula for the linear interpolation without VBA Macro. This is particularly necessary in such a curve fitting as zero curve pricing, where zero rates are required at any points in maturity.

Run Excel Solver multiple times : Period-by-Period Estimations of Nelson-Siegel model

This post shows how to run an Excel solver using VBA macro multiple times. As an example, we take period by period estimations of Nelson-Siegel model.

Excel Solver using VBA macro : Nelson-Siegel yield curve fitting

This post shows how to run an Excel solver using VBA macro. If the number of optimization problems are more than two, every time we switch among these optimization problems, we have to change each settings for solver. It is tedious and may cause key-input errors. In this case, this approach is useful.

Generating Dynamic Multiple Excel Charts by using VBA macro

This post combines a dynamic chart and multiple charts, both of which are covered in two previous post. The purpose of this work is to enhance our productivity and alleviate some burden of repeated routines.

Dynamic Excel Chart by using VBA macro

This post shows how to make a dynamic chart using Excel VBA macro. This dynamic chart adjusts graphs automatically when a length of data is changed.

Generating Excel Charts with different data while keeping the same format and style

This post shows how to generate multiple Excel charts with the same format and style. As the number of charts increases, it is hard to draw them manually. Therefore we need to use Excel VBA macro to handle this problem.

R : httr in R and MSXML2.ServerXMLHTTP in Excel VBA

This post shows a R counterpart of Excel VBA's MSXML2.ServerXMLHTTP related commands with which server APIs are called easily. In case of R, it is done by using httr R package. As an illustration, SQL query for retrieving swap date schedule is executed by calling this server API.

Bond Convexity in Excel and R

A convexity is needed to describe a non-linearity of a bond price, which is absent in a duration. This post explains the meaning and calculation process of the convexity by using Excel and R.

Bond Modified Duration in Excel and R

Bond duration is a basic building block for bond portfolio management and asset-liability management (ALM). This post explains the meaning of duration and calculation of this risk measure by using Excel and R.

Excel : VLOOKUP with a vector of lookup values

This post presents an advanced Excel technique for the quadratic form matrix calculation accompanying cross products with correlation. This approach allow the user to change only input data and get the correct result without modifications of Excel formula.

Pricing of FX Forward in R and Excel

This post explains how to price a FX forward. We assume that 1) USD is the foreign currency and KRW the domestic one, 2) USD IRS zero curve and KRW FX implied zero curve are given. Before making a R code, we use Excel spreadsheet for the clear understanding of the calculation process.

Simple Linear Interpolation without VBA Macro in Excel

This post presents a simple but useful Excel formula for the linear interpolation without VBA Macro. This is most frequently used especially when dealing with repeated zero curve interpolations. Instead of making a VBA macro, we can use the built-in Excel function for linear regression quickly .

RDCOMClient : read and write Excel, and call VBA macro in R

This post gives an short introduction to the RDCOMClient R package, which provides a variety of functionalities. Our focus is on reading from and writing to Excel, and call VBA macro function in R.

How to Use Obba for a link between Java and Excel

This post shows the step by step guide on how to use Obba. Obba is a Java object handler for excel spreadsheet applications. Obba documentations are very good for the intermediate or advanced level. For the beginner or novice, we provide some useful examples for more clear understanding.