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:
- Format the statement generated by Questrade using
format_statement - Review the statement and make changes where necessary
- Convert to OFX using
write_OFXand 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.TORead 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 02adf65d148492035f97a2df4fcb030fBe 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)