In AndyPL22/questradeOFX: Convert Questrade statements to OFX files

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures"
)
library(questradeOFX)

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

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

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)

AndyPL22/questradeOFX documentation built on May 17, 2019, 6:10 a.m.