README.Rmd

---
title: "README"
author: "Andrew Plowright"
date: "April 5, 2019"
output: github_document
---

```{r, include = FALSE}
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.

```{r}
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.

```{r}
# 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.

```{r}
# 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`.

```{r}
# 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.

```{r, eval = FALSE}
# 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)
```