README

Andrew Plowright April 5, 2019

Overview

This package was created to facilitate the conversion of Questrade account statements in Excel to OFX format, which can then be read into Microsoft Money. This operation should be performed in three steps:

1. Format the statement generated by Questrade using format_statement
2. Review the statement and make changes where necessary
3. Convert to OFX using write_OFX and then import into Microsoft Money

Example

Get the file paths for your Questrade statement and a table of your funds. Two sample files are provided with this package.

stmt_path <- system.file("extdata", "sample_statement.xlsx", package = "questradeOFX")
fund_path <- system.file("extdata", "sample_funds.csv",      package = "questradeOFX")

View the funds table. This data.frame must have the name and symbol of all the funds in your Questrade statement.

# Read CSV file of funds
fund_table <- read.csv(fund_path)
fund_table
#>                                     name symbol
#> 1   Vanguard U.S. Total Market Index ETF VUN.TO
#> 2 iShares Core MSCI Emerging Markets ETF XEC.TO
#> 3   iShares Core MSCI EAFE IMI Index ETF XEF.TO

Read the statement and then use format_statement to prepare it for conversion to OFX. This function automatically formats the date/times correctly, identifies the types of transactions and creates unique transaction IDs.

# Read statement from Excel file
stmt <- xlsx::read.xlsx(stmt_path,1, stringsAsFactors = F)

# Format statement correctly
stmt <- format_statement(stmt)
stmt
#>                  date symbol  account
#> 1 2019-01-01 12:00:00    VUN 11111111
#> 2 2019-01-02 12:00:00        22222222
#> 3 2019-01-03 12:00:00        22222222
#> 4 2019-01-04 12:00:00    XEC 11111111
#> 5 2019-01-05 12:00:00    XEC 11111111
#> 6 2019-01-06 12:00:00 XEF.TO 11111111
#> 7 2019-01-07 12:00:00 XEF.TO 11111111
#>                                                                             description
#> 1 VANGUARD U S TOTAL MARKET INDEX ETF TR UNIT DIST ON 605 SHS REC 01/01/19 PAY 01/01/19
#> 2                                                          CON 1111111111 TO 2222222222
#> 3                                                          CON 1111111111 TO 2222222222
#> 4              ISHARES CORE MSCI EMERGING MARKETS IMI INDEX ETF: FROM ACCT 333-33333-33
#> 5                ISHARES CORE MSCI EMERGING MARKETS IMI INDEX ETF: TO ACCT 333-33333-33
#> 6                           ISHARES CORE MSCI EAFE IMI INDEX ETF UNIT WE ACTED AS AGENT
#> 7                           ISHARES CORE MSCI EAFE IMI INDEX ETF UNIT WE ACTED AS AGENT
#>   quantity price commission   amount currency      action      name
#> 1        0   0.0       0.00   100.00      CAD    Dividend Questrade
#> 2        0   0.0       0.00  1500.00      CAD     Deposit Questrade
#> 3        0   0.0       0.00 -1500.00      CAD    Withdraw Questrade
#> 4       50   0.0       0.00     0.00      CAD  TransferIn Questrade
#> 5      -50   0.0       0.00     0.00      CAD TransferOut Questrade
#> 6       75  27.2      -0.25 -2040.25      CAD       BuyMF Questrade
#> 7      -75  27.5      -0.25  2062.25      CAD      SellMF Questrade
#>                             tranID
#> 1 4ac680fc66523cfabb9801a142296cb6
#> 2 63ab0e50651a90a09741d44deec74567
#> 3 2997df9d3157a75aff9aad3086ebf756
#> 4 346eaa2f23a6b2fa43c48a3f1272f926
#> 5 18ec395afb3a183b093098d908987aa2
#> 6 9f0c593507ae840b181794c9a31779ea
#> 7 02adf65d148492035f97a2df4fcb030f

Be sure to carefully review this table and make adjustments when necessary. Questrade is inconsistent with the use of stock symbols, so make sure they match what is contained in fund_table.

# Replace incorrect symbols
stmt[stmt$symbol == "VUN", "symbol"] <- "VUN.TO"
stmt[stmt$symbol == "XEC", "symbol"] <- "XEC.TO"

Once the statement is reviewed, set the statement's date and save to an OFX file. This can now be imported into Microsoft Money.

# Get statement date from file
stmt_date <- file.info(stmt_path)[,"mtime"]

# Write OFX file to disk
write_OFX(stmt, "new_statement.ofx", stmt_date, fund_table)