Tag: Excel

Feed: R-bloggers. Author: sang-heon lee. #========================================================## Quantitative ALM, Financial Econometrics & Derivatives # ML/DL using R, Python, Tensorflow by Sang-Heon Lee ## https://kiandlee.blogspot.com#——————————————————–## A simple Asset/Liability CF Matching problem#========================================================#graphics.off()  # clear all graphsrm(list = ls()) # remove all files from your workspacelibrary(ROI)library(ROI.plugin.neos)# Gross Interest RatesRx = 1.01    # Xi : the line of creditRy = 1.02    # Yi : CP 90dRz = 1.003   # Zi : excess funds# decision variables# –> x1,2,3,4,5, y1,2,3, z1,2,3,4,5, v# Left hand side matrixv.LHS – c(     1,0,0,0,0,    1,0,0,   –1,0,0,0,0,  0,   –Rx,1,0,0,0,    0,1,0,  Rz,–1,0,0,0,  0,    0,–Rx,1,0,0,    0,0,1,  0,Rz,–1,0,0,  0,    0,0,–Rx,1,0,  –Ry,0,0,  0,0,Rz,–1,0,  0,    0,0,0,–Rx,1,  0,–Ry,0,  0,0,0,Rz,–1,  0,    0,0,0,0,–Rx,  0,0,–Ry,  0,0,0,0,Rz,  –1)m.LHS – matrix(v.LHS, nrow = 6, byrow = TRUE)# v (14th decision variable) is the objective functionlp_obj – L_objective(c(rep(0,13),1))# LHS * X = RHSlp_con – L_constraint(    L   = m.LHS,     dir = rep(“==”, 6),    rhs = c(150,100,–200,200,–50,–300))# Lower & Upper bounds for decision variableslp_bound – V_bound(    li = 1:14, ui = 1:14, lb = rep(0,14),     ub = c(rep(100,5), rep(Inf,9)))# Set Problemlp – OP(objective = lp_obj,          constraints = lp_con,          bounds = lp_bound, maximum = TRUE)# Solve Problemopt – ROI_solve(    lp, solver = “neos”,     method = “mosek”,     email = “your email address”)# Printcat(“nResult for simple A/L CF Matching nn”,    “Objective Function Value = “,     round(opt$objval,4), “n”,    “Decision  Variable Xi = “,     round(opt$solution[1:5],4), “n”,    “Decision  Variable Yi = “,     round(opt$solution[6:9],4), “n”,    “Decision  Variable Zi = “,     round(opt$solution[10:14],4)) ... Read More

Feed: R-bloggers. Author: sang-heon lee. [This article was first published on K & L Fintech Modeling, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't. Bond duration is the first order linear approximation and thus does not consider a non-linearity. Making up for this shortcoming of the duration, a convexity is needed to describe the non-linearity of a bond price. This post explains the meaning and calculation process of the convexity by using Excel ... Read More

Feed: R-bloggers. Author: Pritam Dalal. [This article was first published on RStudio Blog, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't. Photo by Clay Banks on Unsplash RStudio is dedicated to our mission to support open source data science, and we believe that a code-first approach is uniquely powerful, because code provides the flexibility to build and share insights, tailored to the analytic problem and the needs of your stakeholders. However, different audiences ... Read More

Feed: Microsoft Power BI Blog | Microsoft Power BI. Author: . We are excited to announce that in the coming weeks we’re expanding the availability of Power BI and Excel connected features such as PivotTables from Power BI datasets and Organization data types to Power BI free license users. This helps organizations who use Power BI Premium to easily distribute authoritative datasets broadly with their users via Excel. Previously, accessing Power BI data directly from Excel (Desktop or Web) required a Power BI Pro or Power BI Premium Per User (PPU) license. Now users who have a Power BI free license ... Read More

Feed: R-bloggers. Author: HighlandR. [This article was first published on HighlandR, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't. Importing multiple excel files (not sheets, but files) and flattening into one data frame / tibble / data.table should be straightforward – shouldn’t it? How many ways can there be? What are the pros and cons of each? I’ve made three copies of an excel file from a PreppinData challenge. For this, I just ... Read More

Feed: R-bloggers. Author: sang-heon lee. #=========================================================================## Financial Econometrics & Derivatives, ML/DL using R, Python, Tensorflow  # by Sang-Heon Lee ## https://kiandlee.blogspot.com#————————————————————————-## FX Forward Pricing#=========================================================================#graphics.off()  # clear all graphsrm(list = ls()) # remove all files from your workspace#————————————————————————–# 1. Market Information#————————————————————————–    #——————————–    # Zero curves as of 2021-06-30    #——————————–    lt.mt – list(        spot_date = 44377,   # spot date        KRWUSD.FX.Spot = 1129.945, # FX Spot Rate        KRW_CRS.zero = data.frame(            # Excel numeric date            date = c(44378, 44379, 44386, 44410,                      44441, 44474, 44564, 44747),             rate = c(0.00629994563073550,                     0.00629994563073550,                     0.00463177425055339,                     0.00329405202619836,                     0.00292249195639071,                     0.00273429173667466,                     0.00331796879551685,                     0.00397939937284654)        ),        USD_IRS.zero  = data.frame(            # Excel numeric date            date = c(44378, 44386, 44410, 44441,                     44473, 44564, 44655, 44747),             rate = c(0.000842844860217754,                     0.000908701294845472,                     0.001006361388394810,                     0.001238311778397180,                     0.001455778457003310,                     0.001533135923937390,                     0.001716219715472820,                     0.001848263900368730)        )    )    #——————————–    # Interpolation of zero curve    #——————————–    # USD IRS    v.date   – lt.mt$USD_IRS.zero$date    v.zero   – lt.mt$USD_IRS.zero$rate    func_linear_USD_IRS.zero – approxfun(v.date, v.zero,                                           method=“linear”)    # KRW CRS    v.date   – lt.mt$KRW_CRS.zero$date    v.zero   – lt.mt$KRW_CRS.zero$rate    func_linear_KRW_CRS.zero – approxfun(v.date, v.zero,                                           method=“linear”)#————————————————————————–# 2. FX Forward Trade Information#————————————————————————–    fAmt – 10000      # Foreign notional amount    dAmt – 10948600   # Domestic notional amount    fPosition – 1     # 1 if Foreign is a buy position, -1 if sell    mat_date  – 44561 # maturity date of FX Forward contract#————————————————————————–# 3. FX Forward Pricing#————————————————————————–    #——————————–    # 1) Foreign    #——————————–    # zero rate for discounting    fzero – func_linear_USD_IRS.zero(mat_date)    # discount factor    fDF – exp(–fzero*(mat_date–lt.mt$spot_date)/365)    # discounted foreign amount denominated in foreign currency    pv_fAmt_in_f – fAmt*fDF    # discounted foreign amount denominated in domestic currency    pv_fAmt_in_d – pv_fAmt_in_f*lt.mt$KRWUSD.FX.Spot    #——————————–    # 2) Domestic    #——————————–    # zero rate for discounting    dzero – func_linear_KRW_CRS.zero(mat_date)    # discount factor    dDF – exp(–dzero*(mat_date–lt.mt$spot_date)/365)    # discounted domestic amount denominated in foreign currency    pv_dAmt_in_d – dAmt*dDF    # discounted domestic amount denominated in domestic currency    pv_dAmt_in_f – pv_dAmt_in_d/lt.mt$KRWUSD.FX.Spot    # 3) Price    #——————————–    price_in_f – fPosition*(pv_fAmt_in_f – pv_dAmt_in_f)    price_in_d – fPosition*(pv_fAmt_in_d – pv_dAmt_in_d)    print(paste0(“FX Forward Price ($) = “, round(price_in_f,2)))    print(paste0(“FX Forward Price (w) = “, round(price_in_d,2))) ... Read More

Feed: R-bloggers. Author: nsaunders. [This article was first published on R – What You're Doing Is Rather Desperate, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't. ShareTweet It’s an old favourite of this blog, isn’t it. We had Gene name errors and Excel: lessons not learned (2012). Followed by Data corruption using Excel: 12+ years and counting (2016). Perhaps most depressingly of all, the conclusion of the trilogy, When your tools are broken, ... Read More

Feed: R-bloggers. Author: sang-heon lee. #=========================================================================## Financial Econometrics & Derivatives, ML/DL using R, Python, Tensorflow  # by Sang-Heon Lee ## https://kiandlee.blogspot.com#————————————————————————-## Read and Write Excel in R, also call VBA macro in R using RDCOMClient## Install package (Not available on CRAN at 12 June 2019)# install.packages(“RDCOMClient”, repos = “http://www.omegahat.net/R”)#=========================================================================#library(RDCOMClient)graphics.off()  # clear all graphsrm(list = ls()) # remove all files from your workspace#===========================================================# functions using RDCOMClient#===========================================================f_read_vector – function(xlWbk1, sheet1, range1){    sheet – xlWbk1$Worksheets(sheet1)    range – sheet$Range(range1)    data  – as.numeric(unlist(range[[“Value”]]))    return(data)}f_write_vector – function(xlWbk1, sheet1, range1, data1) {    sheet – xlWbk1$Worksheets(sheet1)    range – sheet$Range(range1)    range[[“Value”]] – asCOMArray(data1)}#===========================================================# MAIN#===========================================================    # set working directory    setwd(“D:/SHLEE/blog/excel_com”)    # Create Excel Application    xlApp – COMCreate(“Excel.Application”)    # Open the Macro Excel book    fn – “sample_excel.xlsm”    xlWbk – xlApp$Workbooks()$Open(paste0(getwd(),“/”,fn))    # use TRUE for Excel Spreadsheet to be visible    xlApp[[‘Visible’]] – FALSE #===========================================================# Communicate between R and Excel#===========================================================    # Arguments for Excel Spreadsheet and VBA macro    sheet      – “Sheet1”    range_in   – “C3:C12”    range_out  – “D3:D12”    macro_name – “macro1”    #—————-    # Example 1    #—————-    # 1) write input values from R to Excel    data_in – 1:10    f_write_vector(xlWbk, sheet, range_in, data_in)     # 2) run Excel macro    xlApp$Run(macro_name)    # 3) read output values from R to Excel    data_out – f_read_vector(xlWbk, sheet, range_out)    print(cbind(data_in, data_out))    #—————-    # Example 2    #—————-    data_in – rnorm(10)    f_write_vector(xlWbk, sheet, range_in, data_in)     xlApp$Run(macro_name)    data_out – f_read_vector(xlWbk, sheet, range_out)    print(cbind(data_in, data_out))#===========================================================# save and quit#===========================================================xlWbk$close(TRUE); xlApp$Quit()  ... Read More

Feed: R-bloggers. Author: finnstats. [This article was first published on Methods – finnstats, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't. Reading Data From Excel Files into R, so many people still saving their dataset in R but sometimes coming to data analysis facing lots of difficulties, while loading data set into R, we can make use of the power of R functions. In this tutorial we are going to describe how to ... Read More

Feed: R-bloggers. Author: George Mount. [This article was first published on George J. Mount, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't. The Clark’s Nutcracker has landed! That’s the bird on the cover of Advancing into Analytics, copies of which I received the other day. There’s that new author enthusiasm! With my new book, Advancing into Analytics You can now get your digital or paperback copy too at your bookseller of choice. Learn ... Read More