Tracker for NEO exchange prices

A couple of securities I track using Google Sheets have moved to the NEO Exchange. Google Finance functions don't know about this small exchange so I wrote something to track prices by scraping its website, and providing some CSV data on current prices, moving averages, and sparklines.

Initial install

yarn install --offline

Create initial database

# you backed this up right?
rm database.sqlite
yarn node scripts/migrate.js

Import data

XXX_raw.csv is from copying from Historical data from my brokerage website, pasting into a spreadsheet application (Numbers on macOS worked fine), and then exporting to CSV. Data was copied from TSX the year up to and including 2017-Feb-21, then data from 2017-Feb-22 to the current date was added.

# tidy up data
scripts/convert.pl data/CLU_raw.csv > data/CLU.csv
scripts/convert.pl data/CRQ_raw.csv > data/CRQ.csv

# import into database
scripts/import-csv.sh data/CLU.csv CLU 'iSHARES US FUNDAMENTAL INDEX ETF'
scripts/import-csv.sh data/CRQ.csv CRQ 'iSHARES CANADIAN FUNDAMENTAL INDEX ETF'

Import AlphaVantage data

Create a file containing your AlphaVantage api key:

echo "ABC123" > alpha_vantage_key.txt

export APIKEY=$(cat alpha_vantage_key.txt)
scripts/import-from-alphavantage.sh $APIKEY "SPDR Sector XLK" XLK

Start the app

# start for the first time
yarn pm2 start app.js

# make pm2 remember to run the app
yarn pm2 save

# install with systemd - follow instructions
yarn pm2 startup system

# note that upgrading node versions may have a problem
# with compiled modules; pm2 also keeps env variables around
# from its last invocation.  Maybe try:

yarn pm2 restart --update-env

Set up scheduled requests

# make the backup dir for the database
mkdir backups

# .cron extension inside of cron.d will make cron ignore the file!
# check that the username and path to the sqlite3-backup.sh script are correct!
sudo cp cron/neo-tracker /etc/cron.d/neo-tracker

Routes

GET

• /current/SYMBOL - returns as a CSV line, the following in order:
  1. 52-week low
  2. 52-week high
  3. 50-day MA
  4. 200-day MA
  5. current price
• /sparkline/month/SYMBOL - returns as CSV, date and closing price for the past month for the given symbol
• /sparkline/year/SYMBOL - returns as CSV, date and closing price for the past year (Mondays only) for the given symbol
• /all - returns as CSV, the following, for all tracked symbols:
  1. symbol
  2. 52-week low
  3. 52-week high
  4. 50-day MA
  5. 200-day MA
  6. current price

POST

• /update/current - fetch prices and update the locally stored prices. Should be run every hour or so on trading days

Some TODO

• more server maintenance stuff:
  • etckeeper
  • logwatch
• authentication middleware

Development

Run the server on port 3000, with watching of files and auto-restarting:

yarn dev-server