The Absolute Beginning for Amazon Alexa Voice Skill Development

An Introduction to this Alexa Learning Journey

So if you’ve visited my site at all in the past, you’ll notice this is the most blogging I’ve done in…ever? Put another way, a significant confluence of factors has dialed up my motivation to doggedly pursue learning and growth. So, here we are at learning Alexa skills!

A few weeks ago, I was suckered by the cheap promise of a free hoodie from Amazon, in exchange for building and publishing my first Amazon skill. You can experience the equally cheap output of that effort by saying, “Alexa, start silly marketing strategies” (again and again.)

Is it a good skill? No! Nor am I going to pull a Dollar Shave Club here either. It’s a bad skill! It’s not interactive. You just have to keep asking it over and over for some dumb buzzword-laden sentences. I promise I only account for 75% of the utterances. 🙂

2017-01-25-001-Silly-Marketing-Strategies

Honestly, I’m thrilled to have published an Alexa skill. But there’s so much more out there! Thus, I’m embarking on yet another educational journey, this one into Amazon Alexa Voice Skill Development.

Getting Set Up with Resources You Need for Alexa Skill Development

The instructions below are for PC only. Apologies, Mac users!

  1. You need to create a folder directory in which we’ll be housing our various materials and code.
  2. Visit the Alexa Skills Kit JS Git Hub page and download all materials as a ZIP.
    1. 2017-01-25-002-Alexa-Skills-Kit-JS-Git-Hub
  3. Once you’ve downloaded the ZIP file, move it from your default Downloads directory, and into the folder you created in Step 1.
  4. Extract the ZIP file. The unzipped folder should be named, by default, “alexa-skills-kit-js-master”. Within the unzipped folder is yet another folder of the same name.
  5. Take the all the contents within the two folders described above, and move them into the “Alexa” directory, higher up.
    1. Move the contents from the “samples” folder into the main “Alexa” directory, so the skill folders (spaceGeek, reindeerGames, etc.) are in the umbrella directory.
  6. When you’ve completed Step 5, you should be left with A) Three text files, and a bunch of skill folders, B) an empty “samples” folder [A&B you moved up two directories into the “Alexa” umbrella folder], C) an empty “alexa-skills-kit-js-master” folder and D) the original zip file.
  7. Delete items B, C, and D from step 6.
  8. Download and install a code/text editor. I personally prefer Sublime Text 2, but a lot of folks prefer Notepad++ as well.
    1. A dedicated code/text editor is highly preferable here, as much of the code for the Alexa skills in JS – Node.JS in particular, I believe.
  9. After you’ve muddled your way through this folder architecture, go into the README file and not the order of Skills. Number the skill folders accordingly in the umbrella directory, excluding helloWorld. (You should have skills 1-9, starting with spaceGeek and ending with ChemistryFlashCards.)

2017-01-25-003-Alexa-Folder-Structure

Alright, that’s it for now! Next, we’ll look at setting up access to Amazon Developer and Amazon Web Services.

DSC_0037 (2) Zach Doty Cover Photo for SQL SELECT Clauses & Statements

The SELECT Clause, SQL Statement Fundamentals

Getting Started in SQL Statement Fundamentals

Howdy, all, welcome back to our journey learning SQL. This post will deal with basic SQL statements. In fact, most of these SQL statements should be applicable to most major types of SQL databases (MySQL, Oracle, and so forth.)

The SELECT Statement (or Clause)

First up, we’ll start with the “Hello World” of SQL: SELECT. We’ll look at the formal conventions of the SELECT statement and some examples using the statement. A quick aside: SELECT is also often known as a clause in SQL settings. For the purpose of this article, the educational materials I’m walking through proposes clause and statement may be used interchangeably for these purposes.

SELECT is one of the most common tasks in querying data tables with SQL. Further, it has many clauses that may be combined to form a powerful query. Let’s look at the basic form of a SELECT statement. Below, you will use the SELECT statement to call in a column or some column names, separated by a comma if multiple columns, then FROM a table.

SELECT column1,column2,column3 FROM table_name;

So, breaking down again the select statement.

  1. Specify a list of columns in the table which you want to query via the SELECT clause
  2. Use a comma between each column you are querying, if multiple columns
    1. If you want to query all columns in a data, save yourself some time by using the * asterisk wildcard as a shortcut for selecting all columns
  3. After you’ve called in the appropriate columns in the SELECT clause, follow it with FROM, where you indicate the appropriate table name

 

Sidebar 1: Random facts about the SELECT statement and SQL language

Time for a TV timeout! Did you know that that the SQL language is case insensitive? So if you use “SELECT” or “select”, you should get the same results. For the purposes of this education and sharing, SQL clauses / keywords / statements will be typed in all uppercase caps to make the code easier to read and stand out among all this text. 🙂

Okay, just one more sidebar note! It’s generally not encouraged to use the asterisk (*) select all columns wildcard in queries. Why? If you have a robust table with a ton of columns and a great depth of data beneath those columns, you could be placing unnecessary load on yourself, the SQL server and the SQL application (pgAdmin / PostgreSQL).

 

Application Example 1 for the SELECT Statement

Let’s jump into executing actual SQL commands against databases in pgAdmin!

Below, I’m going to open the file tree, select “dvdrental”, then click “Tools” in the top menu, and select “Query Tool” to execute arbitrary SQL queries and statements.

 2017-01-22-001-pgAdmin4-PostgreSQL-Open-Query-Tool

You should then see the screen below if you are in pgAdmin 4. If you are in pgAdmin 3, then it should appear as a new window.

2017-01-22-002-pgAdmin4-PostgreSQL-Query-Tool-Start

Let’s have some fun, why not go against our own advice and query a whole table? Below, you can see in the query window, we’ve typed:

SELECT * FROM actor;

Into layman’s terms from above, we’re selecting (SELECT) all columns (*) from (FROM) table actor (actor).

Important: My image example doesn’t show it below, please, put a semicolon at the end of the line! (I got hasty making screen shots. 🙂 )

2017-01-22-003-pgAdmin4-PostgreSQL-Query-All-Columns-Actor

After you’ve typed the query, go to the lightning bolt above the window, and click “Execute/Refresh”. I’m just going to punch F5, because I’m about that keyboard shortcut life. In the future, I’ll likely introduce a command or action, note its keyboard shortcut and use that shortcut moving forward for any other examples.

The query should run and refresh. I now have a new tab in pgAdmin, with data output returned from my query. Let’s take a look below.

2017-01-22-004-pgAdmin4-PostgreSQL-Query-All-Columns-Actor-Execute

Okay, so we’ve got four columns returned: actor_id, first_name, last_name, and a last_updated. You’ll also note that below the column names are quick descriptions of the data type for each column. And of course, we see our beloved celebrity data returned below, all 200 rows.

Let’s examine further the data types listed below each column name. The integer below actor_id is pretty simple, numbers. Next, the character varying, below first_name and last_name. Character varying is essentially just string text. The (45) denotes the limit on character count length. Last, the timestame with YYYY-MM-DD and military style HH:MM:SS.XX time, without time zone. We won’t worry too much about the timestamp for now.

If you’re somewhat knowledgeable in SQL, you may rightly decry our glossing over of data types. For beginners, data types will be covered in more detail later. Data types will become increasingly important later, as we execute statements such as, WHERE, in which data types make or break the query. Promise, we’ll cover data types in more detail later.

 

Application Example 2 for the SELECT Statement

So we kind of broke our rules in the first SELECT statement SQL query example. However, some rules were made to be bent or broken, yes? In this example, we’ll follow best practices a bit more closely and select a column or columns by name from a table within the dvdrental database.

Remembering our SELECT column1,column2,column3 FROM table_name format, consider the below, and see it typed in (with closing semicolon on the statement!

SELECT first_name,last_name FROM actor;

2017-01-22-006-pgAdmin4-PostgreSQL-Query-Two-Columns-Actor

Before we execute and refresh via F5, please note that I’ve not included spaces between the column names and comma in the statement. Alright, below is what we see when we execute and refresh.

2017-01-22-007-pgAdmin4-PostgreSQL-Query-Two-Columns-Actor-Execute

In our screen shot, we see at bottom right, confirmation of the query execution. In the output window, we’ll only see what was queried: first_name, last_name. So we’ve left out the actor_id and last_updated columns.

One more note on our output, you’ll notice that all 200 rows were returned for this query. If you think about enterprise level data, that could be 200 million rows, zoinks! As we progress through our material, we’ll look at the aforementioned WHERE statements and other conditions / methods to limit or control the rows in query output.

Perfect Practice Makes Perfect

For the educational benefit, we’ll reinforce and apply what we’ve learned one more time. Let’s say that we’re a business and marketing analyst back in time when DVDs were still used (it’s okay to laugh!) We need to send a New Year’s promotional email (It’s January 2017 when this post was originally published) to all existing customers. We’re going to build and execute a query to that effect.

Below, you can see we’re still in the dvdrental database, in the arbitrary query code input window, with statement: SELECT first_name,last_name,email FROM customer;

2017-01-22-008-pgAdmin4-PostgreSQL-Application-Query-Existing-Customers

One last quick note on syntax and formatting: you can go multi-line! In the below screenshot, we have typed the same query, but added formatting. Explained: SQL will read your code as one line until it runs into the closing semi-colon (;). A common practice is that for every keyword, a new line is created in the query. (Of course, the statement is not closed via semi-colon until appropriate.) I’ve also taken one more step below from various ranging coding practices (CSS, C++, etc.) and indented the ongoing portion of the query to help visually break up the code a bit.

2017-01-22-009-pgAdmin4-PostgreSQL-Application-Query-Existing-Customers-Multi-Line

Wrap-Up

Woohoo, we did it! We ran our first basic SQL queries in pgAdmin / PostgreSQL. We learned how to select all columns within a table and select separate desired columns within a table. Be sure to re-visit my other articles on learning SQL, visit the previous article on restoring an SQL database with table schema only.

In our next post, we’ll learn about using a SELECT DISTINCT statement.

Restoring a SQL Database with Table Schema Only in pgAdmin x PostgreSQL

Howdy! Welcome back to our shared journey of learning SQL. Last time, we learned about creating, deleting and completely restoring SQL databases in pgAdmin. Today, we’ll learn about how to restore a database, but only its table schema.

Specifically, we’ll restore the table names and preferences for types of data within those tables. However, the actual data itself won’t be ported in.

Think of it as taking your house or apartment, and recreating, but you don’t move in with the furniture. (Maybe a sibling instead? 🙂 ) According to the material I’m working through, this method of database restoration is very common and is something we should have down pat.

 

Database Table Schema-Only Restoration, Method 1

An easy method to start this is to right click on the Databases header near the top of the file tree, and to select “Create” > “Database”. With a fresh new database, we’ll have flexibility to do some more management on the “front” side of things. (Knowing I may be abusing terminology a bit, but makes sense to me while writing this.)

 

2017-01-21-001-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-P1

For this example, I’ve finished the new database creation by naming (“OnlySchema”) and saving a new database. Below, you can see the new database in the file tree. Also, if we click through the tree as such, “Schemas” > “public” > “Tables”, we’ll notice there are no tables! (Compare that path exploration to the dvdrental database tables, where you can find 15 tables.)

 

2017-01-21-002-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-P2       2017-01-21-003-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p3

Anyway, let’s get on with restoring table schema only!

  1. Right click on “OnlySchema” and select “Restore”
  2. Select the “Custom or tar” option for the Format field
  3. Select the file via the dialogue, or paste in your file path
    • Up to this point, you should notice we’ve taken the exact same steps for a full database restore. However, #4 is where things are slightly different. Pay attention!
  4. As shown below, click “Restore Options”, and activate the radio button for “Only schema” to yes
  5. Click “Restore” and refresh!

 

2017-01-21-004-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p4

After the refresh, we should see something like below, where if we select the tables option and view “Properties”, we’ll see 15 empty named tables.

 

2017-01-21-005-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p5

 

Method 2: Schema-Only Restoration onto a Database with existing tables, data

What if we’re working with a database that contains existing tables and data? And suppose we need to restore only the schema? Perhaps an error was committed in formatting / management and must be corrected.

Fortunately, this method of restoration is extremely similar to Method 1. For this scenario, there’s one added step toward the end:

  1. Right click on “OnlySchema” and select “Restore”
  2. Select the “Custom or tar” option for the Format field
  3. Select the file via the dialogue, or paste in your file path
  4. Click “Restore Options”, and activate the radio button for “Only schema” to yes
    • Here’s our jump off point, the next step is what differs from Method 1.
  5. Scroll down the dialogue box slightly. You should see a field and radio button for “Clean before restore”. Activate the radio button to “Yes”
  6. Click “Restore” and refresh!

 

2017-01-21-006-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p6

After running the restore job and refreshing, we can verify the Schema Only and data clean prior to restoration occurred correctly. Below, we can click through the file tree down from “dvdrental” > “Schemas” > “public” > “Tables” and check the table properties to see that there are zero (0) rows of data.

 

2017-01-21-007-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p7

 

Wrap Up

I do hope these articles are serving a useful introduction to navigating pgAdmin, and getting familar with the foundations of PostgreSQL and databases. In the forthcoming articles, we’ll start learning about basic SQL syntax. To keep track of all the shared SQL posts and learnings, visit the SQL Education page for a list of articles to date. Cheers!

DSC_0049 Zach Doty Cover Photo for Successful Algo Trading

What Does Successful Algorithmic Trading Look Like?

Before we begin, you’ll notice this post deals with finance and investing, both of which are highly risky. This site and this post is strictly educational. Nothing in this post or on this website is intended as, nor should be construed as investment advice. Kindly first visit the Disclaimers page before you proceed, taking careful note of CFTC Rule 4.41 regarding hypothetical performance. Further, investing and trading is extremely risky, to the point that 90% of participants rapidly fail. As such, your participation in investing and trading could lead to loss of principal, and even further losses beyond that in some cases. Neither this site in its entirety, nor this post, nor any portion thereof, nor any resulting discussion or correspondence related to this site and post, is intended to be a recommendation to invest or trade mutual funds, stocks, commodities, options, or any other financial instrument. I will not accept any responsibility for any losses which might result from applications of ideas expressed on this site or in this post from the techniques or systems mentioned on this site or in this post. Nothing shown is the result of an actual trade. Neither past actual performance, not simulations of performance assure future results, profitable or otherwise.

Introducing this Discussion on Algo Trading Expectations

Welcome back to the journey into algorithmic trading. Hot on the heels of an introductory article dealing with algorithmic trading concepts, and a tactical discussion on algorithmic trading software platforms, we arrive at a mental and philosophical reflection point.

This endeavor into algorithmic trading is about pushing the limits of learning, and applying a broad array of market theory concepts through a scientific lens, mathematics / statistics and computer science. However, a less popular area of algorithmic trading is psychology. Yes, psychology and the mental approach to trading are real areas, even if you’re performing 100% automated, systematic and unattended algo trading.

Well-tread ground among retail traders (non-institutional, non-hedge fund traders) is the pursuit of a “holy grail”. We are bombarded by messages everywhere, in many fields of life. Buy this, use that, and your problems will vanish. Okay, perhaps that’s an oversimplification, but I think you get the point. Onward.

The Definition (and myth) of an Algo Trading “holy grail”

In both active (discretionary) trading and algorithmic trading, the “holy grail” is usually defined as a trading strategy that is has high long term profitability with consistent returns. In short, it is the ultimate trading strategy.

Spoiler alert: the “holy grail” (based on this definition) doesn’t exist. Market regimes change, trends start, trends stop, ranges form, consolidate and eventually break out into new patterns. Even Ray Dalio’s “all weather ” hedge fund strategy was arbitraged away by a changing climate in interest rates, thanks to widespread central bank intervention on the global economy.

This begs the question, is there a “holy grail”, and if so, what is it?

A New Definition of the Algo Trading “holy grail”

Here’s a new definition: The trader can be the “holy grail”.

Long term profitability with consistent returns can be a state of existence for investors and traders. Examples abound if you study the market. However, this is not due to a single algorithm or trading strategy. The trader can be his or her own secret ingredient by doing 3 things:

 

1) Understanding their portfolio of algorithmic trading strategies

2) Understanding how to effectively design, backtest and optimize trading strategies

3) Understanding when to maintain their portfolio, when to execute, modify or shut down strategies according to changing market regimes

 

Why Does This Matter in Algorithmic Trading?

We are all subject to emotions, pressure, stressors and thoughts in all we do. Even if you have a black box algo strategy which only involves you checking the account balance periodically, I can guarantee you psychology comes into play. When (not if) your system(s) enter into drawdown (read: you lose money), your head will be full of surprise, sadness, anger and so on. Even in this scenario, especially in this scenario, your management of expectations and psychology will be the true long determinant of success.

Short version: if you expect one or two algo programs to be your ticket to decades of success, you are sorely mistaken.

Wrap Up

Thanks for sticking through this brief, idealistic conversation on trading psychology and successful trading. In my next article walking through algo trading, I’ll be looking at setting up the MetaTrader4 platform for some basic tasks.

DSC_0322 Zach Doty Cover Photo for SQL Database Creation, Restoration, Deletion

Creating, Restoring and Deleting SQL Databases with pgAdmin 4

Howdy! Let’s continue the journey into learning Structured Query Language (or SQL). Previously, I shared my notes on the absolute beginning point for starting SQL, and also getting started with PostgreSQl and pgAdmin.
Now, we venture into some basics of database management via SQL:
• Creating a database with pgAdmin
• Restoring a database with SQL commands
• Deleting a database

Creating a new SQL Database in pgAdmin via Graphical User Interface

So, let’s retrace our steps on creating a new database. After you’ve successfully started pgAdmin and accessed your databases, right click “Databases” and go to “Create” > “Database”. Then choose your desired name and save.

2017-01-16-001-pgAdmin4-PostgreSQL-Create-Additional-Database

Give it a second, and your brand spankin’ new database should be created! (Alternate method, you could select the “postgres” database in the file tree and select the option to create a new, arbitrary SQL query. In the Query window, type:

CREATE DATABASE dvdtwo;

2017-01-16-002-pgAdmin4-PostgreSQL-Create-Additional-Database-Query-Tool

If you run and refresh you should see the output of the code’s successful run, with no results, and a new database in the file tree. Joy!

2017-01-16-003-pgAdmin4-PostgreSQL-Create-Additional-Database-Query-Tool-Success

 

Deleting (Dropping) Databases in pgAdmin

Let’s build some character and delete the database we just created! Fortunately, deleting (also known as dropping a table is super simple in pgAdmin / PostgreSQL. Below, in the interface, we right click the name of the newly created database and click “Delete/Drop”, and click Okay. Obviously, be sure you don’t do this accidentally.

2017-01-16-004-pgAdmin4-PostgreSQL-Delete-Drop-Database-User-Interface

 

Gracefully Restoring a Database

In the second SQL article, we previously restored a database. We’ll revisit that process in the pgAdmin interface again here. Practice, practice, practice! Let’s restore the dvdtwo database created earlier in this article for good practice. Below, we’ll right click the “dvdtwo” tree header and select the “Restore” option.

2017-01-16-005-pgAdmin4-PostgreSQL-Restoring-Database-2-User-Interface

In the resulting dialogue box, I attempted to use the interface’s file browser. However, I was met with some bugs/error messages. Thus, I copied the file path from Windows Explorer, pasted it into the file path name field and began the restore job. It took a few moments to run, then I refreshed the database and saw tables populated under dvdtwo > Schemas > public > tables. Hooray!

Wrap-Up

Thanks for tuning in! Next article, I’ll share my learnings for restoring a database, but with only the table schema. So to be more specific, I’ll restore the framing and architecture of the sample database, but without the data. This seems a bit like copying and duplicating sheets in Excel, but with blanks for data, so you can populate at your discretion. See you soon.

 

PS- If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.

DSC_0291 Zach Doty Cover Photo for Installing SQl Software PostgreSQl and pgAdmin

Downloading, Installing and Getting Started with PostgreSQL and pgAdmin

How to Download, Install and Get Started with PostgreSQL & pgAdmin, Popular SQL Programming Software

 

Introduction

Welcome back to the grand SQL journey! If you missed the beginning, here’s my previous post as a complete beginner to SQL. In this post, you will:

  • Learn what pgAdmin is
  • Become familiar with the program and some of its release history and
  • Walk through an installation of pgAdmin.

 

What is the pgAdmin SQL Software all about?

According to the educational material I’m working through, it’s commonplace (in some circles) to use a graphical user interface (GUI) when working with a SQL engine. (Compared to working with command line, zoinks!) That being said, pgAdmin is reportedly one of the most popular interfaces for PostgreSQL, the SQL engine / platform of choice listed out in the previous post. For the purposes of our education, I’ll be working in pg Admin 4.

 

Installation of PostgreSQL and pgAdmin

Here are the steps:

  • Install PostgreSQL and pgAdmin through their default Windows install methods. (Sorry Mac and Linux users, this party is PC only for now!)
  • Download a .tar file to be used for sample learning
  • Use the .tar file to restore a database we’ll be working with

 

Downloading PostgreSQL

First, head to https://www.postgresql.org/ in a new tab or window. When you’ve arrived, you should see something like the below screen, and click the “Download” link in the navigation.

2017-01-14-001-PostgreSQL-Download-Home-Screen-Shot

After arriving at the Download page, you should see the below. Under the “Binary Packages” for pre-built packages, select your appropriate operating system. Windows is shown below for the purposes of this demonstration. If you feel led or qualified to choose the source code option, you probably don’t need to be reading this article anyway. 🙂

2017-01-14-002-PostgreSQL-Download-Page-Windows-Install-Screen-Shot

Following the Windows link should take you to the Windows Installers page.  Under “Interactive installer by EnterpriseDB, click the “Download the installer” link. Another option for “Graphical installer by BigSQL” exists, but there have been some anecdotes of annoyances with the BigSQL experience.

2017-01-14-003-PostgreSQL-Download-Page-Windows-Installer-EnterpriseDB

Clicking on the link illustrated above should take you to the EnterpriseDB site, where a screen like the below should appear. When you first arrive, you will be prompted to select a version of PostgreSQL and your machine’s operating system details. Below, I’ve selected PostgreSQL 9.6.1 and Windows x86-32, because I’ve got a really old laptop! A quick note on the PostgreSQL version selection, you should choose the latest non-Beta version to install. As this post regards software and tech, it could quickly become outdated. When you have selected the appropriate details, click the ever-important “DOWNLOAD NOW” button.

2017-01-14-004-EnterpriseDB-PostgreSQL-Download-Page-Windows-Installer

After the .exe file downloads, open it, allow it to run. A screen for Microsoft Visual C++ Redistributable will likely appear, let it run and complete. Next, the PostgreSQL installation dialogue should appear. (Below.)

2017-01-14-005-EnterpriseDB-PostgreSQL-Installation-Starter

Accept the default installation and data directories, unless you have a weird and specific reason to do something else. After you do this, enter your desired password.

A quick and necessary aside. Please follow best practices for passwords and information security. It is your sole responsibility to do so: protecting your information and yourself. I accept neither liability nor responsibility, nor in whole, nor in part, for your password security. Be smart.

Okay, with that out of the way, proceed to the Port portion of Setup. Accept the default and proceed. After the port screen comes a locale configuration. Accept the default and proceed. Now, you should be to start the installation process. Click “Next >”!

Once you click install, you should see a pretty traditional installation progress screen that creates a ton of directories, unpacks HTML files, and so on. (Note: this took my machine 5 minutes or so to install.) If you care to do so, uncheck any mail subscription offers and finish the installation!

From here, you should be able to get to pgAdmin 4 from your Windows start menu. Tah-dah!

2017-01-15-006-pgAdmin4-PostgreSQL-Installation-Starter

If you expand the servers icon, you ought to see something along the lines of PostgreSQL9.6. If you click to expand the PostgreSQL 9.6 item, you should be prompted to enter your password. From there, you should see a dashboard with some various charts becoming populated, like below.

2017-01-15-007-pgAdmin4-PostgreSQL-Installation-Confirmation

Next, a sample database will be restored. As shown below, please right click “Databases”, then mouse over the menu item “Create” and click “Database”.

2017-01-15-008-pgAdmin4-PostgreSQL-Database-Creation

From there, name your database and save it! Once it’s successfully created, you should see a new part of the tree for your database, with options underneath such as Casts, Catalogs, Event Triggers, Extensions and so on.

2017-01-15-010-pgAdmin4-PostgreSQL-Database-Confirmation

To restore data into the database, right click the database name and select the restore option. First time users, you may see a dialogue box saying, “Configuration Required”. No worries here, we’ll get through that momentarily. Click File > Preferences > Paths > Binary Paths.

2017-01-15-012-pgAdmin4-PostgreSQL-Database-Binary-Paths

Once you’ve reached the point as described and shown above, you’ll need to open a new Explorer window. (Sorry, Mac and Linux folks, no guidance from me here.) Go to Computer > Program Files > PostgreSQL > 9.6 > bin. Copy the address and paste into the pgAdmin PostgreSQL Binary Path, click OK.

2017-01-15-013-pgAdmin4-PostgreSQL-Database-Binary-Paths-Computer

That should now allow you to restore a database. Now return to the above steps, and when you select restore, select the options as desired. You should see a processing dialogue and success message. When this is complete, right click the database name and select “Refresh”. Very important – think of like you needing to refresh the data sources when working with pivot tables in Microsoft Excel.

Okay, you should have your first database! right click the database name and select “Query Tool”. Let’s make our first SQL query FTW!

2017-01-15-014-pgAdmin4-PostgreSQL-Database-Success-First-Query

In the code window, type:

SELECT * FROM [table “” not found /]
;

Then, either select the lightning bolt and execute + refresh. Or, you could just press F5 and it accomplishes the same thing. If the query is successful and you didn’t misspell a simple table name like I did the first time, then you should see the table in the Data Output window.

 

Wrap-Up

Alrighty! How about a rip-roaring start into SQL? Excited to keep learning with you. Stay tuned. If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.

 

 

DSC_0079 Algo trading cover image by Zach Doty

MetaTrader4 (MT4) Software: A Beginner’s Algorithmic Trading Platform

Before we begin, you’ll notice this post deals with finance and investing, both of which are highly risky. This site and this post is strictly educational. Nothing in this post or on this website is intended as, nor should be construed as investment advice. Kindly first visit the Disclaimers page before you proceed, taking careful note of CFTC Rule 4.41 regarding hypothetical performance. Further, investing and trading is extremely risky, to the point that 90% of participants rapidly fail. As such, your participation in investing and trading could lead to loss of principal, and even further losses beyond that in some cases. Neither this site in its entirety, nor this post, nor any portion thereof, nor any resulting discussion or correspondence related to this site and post, is intended to be a recommendation to invest or trade mutual funds, stocks, commodities, options, or any other financial instrument. I will not accept any responsibility for any losses which might result from applications of ideas expressed on this site or in this post from the techniques or systems mentioned on this site or in this post. Nothing shown is the result of an actual trade. Neither past actual performance, not simulations of performance assure future results, profitable or otherwise.

Introduction: Why MetaTrader 4 (MT4) as a Beginning Algo Trading Platform

Alright! With the obligatory legalese out of the way, my journey into algorithmic trading and quantitative finance continues. Obviously to trade, you need to have a vehicle for doing so. Unlike the days of old where you would call a stock broker, most trading today is electronic. For many electronic trading use cases, you need:

  • A platform that at a minimum provides an electronic order management system connected to a broker
  • Who then deals with an exchange (Chicago Mercantile Exchange [CME], New York Stock Exchange [NYSE])
  • That eventually attempts to fulfill your request and back.

There are tons of software platforms and configurations which can be used to design and develop an algorithmic trading program. For the purposes of this education, MetaTrader 4 was selected as the starting platform. Though I’ve dealt with some other programs in the past such as NinjaTrader, yes, Robinhood (sigh), and TD Ameritrade’s ThinkorSwim, MT4 is the platform for this foray.

Advantages

  • Free (Obviously an important reason! Other platforms offer free trials, but with limited functionality.)
  • Free data (Close second for the most important reason. If your industry is remotely related to technology, you understand data is the new oil. While some vendors may provide free end of day data (E.g., Kinetick), what you need often has a price or cost associated with it. MT4 will offer viable free and live data, helpful to the beginner.)

For Mt4, we will get free live data from the Broker. Additionally, historical data is available from MetaQuotes and other online sources like Dukascopy.

  • Access to different markets (With some footwork, we can test and trade FX, Equities Indices, Equities (Stock), Commodities and Fixed Income instruments.
  • Ease of learning the coding language (The MQL4 language is largely based on the widespread C++ programming language. In addition to its popular base, MetaQuotes Language (MQL4) is well documented with lots of users, so many examples to learn from!)

 

Disadvantages

  • Lowest bar timeframe is 1min (If you’re doing more precise or higher frequency trading, this can be a drawback. However, A), we’re not doing HFT, B) if we were, there is a workaround. And we probably would some kind of integration with Rithmic for HFT-esque executions.)
  • Portfolio backtesting doesn’t come out of the box (If you’re simulating a multi-instrument algo strategy (Or in MT4 terms, Expert Advisor [EA]) you’ll be out of luck, and would have to test on another platform or find another methodology.)
  • Advanced statistical analysis doesn’t come out of the box either (It can be difficult to incorporate heavy statistical apparatuses in the algos. However, we can carry out statistical analysis on the data using Microsoft Excel or other statistical software (Python libraries, etc.). Performance analysis can be done using 3rd party software, more on that in the later.)

 

 

Other Possible Trading Software Platforms

 

Various reasons the above platforms listed aren’t being used includes, but not limited to:

1) Cost

2) Difficulty of learning the coding languages

3) Complicated user interfaces

4) Backtester and/or optimizer not included.

5) Software platform not well designed for building and trading algo strategies.

6) Limited access to markets

7) Poor community support and documentation

 

Why not MT5?

The main reason MT4 is being used over MT5 is because most brokers support the former but not the latter.

Some key differences between MT4 and MT5:

1) Different coding language (MQL4 vs MQL5). MQL5 is more difficult to pick up for beginners.

2) MT4 has been around longer and has better online support. There are many code libraries, templates and examples for MT4.

 

3) MT5 does not allow external data import (This is unfortunate- data management is a huge component of backtesting.)

 

Final Note: Why Forex as a Trading Instrument?

Convenience: Forex data isn’t (as) affected by many common market variables such as stock splits, futures contract roll-overs, expiries and dividends, etc. These factors can greatly complicate the testing and data cleaning process.

Data is available: Free data everywhere! Data on other instruments is much harder to obtain.

Can often extrapolate to other instruments: Algos built on forex can often be easily modified to fit CFDs on other instruments.

Principal instrument traded on MT4.

 

Wrap-Up

Thanks for tuning in- if you’re weird enough to like this, you might find my beginner’s foray into SQL interesting as well. Cheers!

Zach Doty Photograph DSC_0018

Getting Started With Algorithmic Trading

One Introduction to Quantitative Finance, via Algorithmic Trading

Before we begin, you’ll notice this post deals with finance and investing, both of which are highly risky. This site and this post is strictly educational. Nothing in this post or on this website is intended as, nor should be construed as investment advice. Kindly first visit the Disclaimers page before you proceed, taking careful note of CFTC Rule 4.41 regarding hypothetical performance. Further, investing and trading is extremely risky, to the point that 90% of participants rapidly fail. As such, your participation in investing and trading could lead to loss of principal, and even further losses beyond that in some cases. Neither this site in its entirety, nor this post, nor any portion thereof, nor any resulting discussion or correspondence related to this site and post, is intended to be a recommendation to invest or trade mutual funds, stocks, commodities, options, or any other financial instrument. I will not accept any responsibility for any losses which might result from applications of ideas expressed on this site or in this post from the techniques or systems mentioned on this site or in this post. Nothing shown is the result of an actual trade. Neither past actual performance, not simulations of performance assure future results, profitable or otherwise.

Why Am I Writing This?

Similar to my Absolute Beginning Steps for Learning SQL, I’m embarking on a number of education journeys in 2017. Another journey, seen here, is algorithmic trading. Loosely known by several monikers (below), this is an intensive field that may be both challenging and rewarding. As self-accountability and sharing, I’m posting what I learn as I progress.

 

What is Algorithmic Trading? (AKA Automated, Quantitative or Systematic Trading)

Put simply, algorithmic trading (quantitative trading) is trading financial instruments via computers, through logic, programs and preset conditions.

 

Types of Algorithmic Trading

For the purposes of this post, there are two types of algorithmic (algo) trading. 1) High frequency trading (HFT), and, 2) Low frequency trading.

High Frequency Trading (HFT): Speed is the absolute and expensive edge. Co-located servers in New York and Chicago run proprietary code on custom computer hardware, relying on fiber and microwave communications for trading movements that happen in less than a second. Having tried a variant of HFT in 2016, saying HFT is difficult is probably the understatement of the decade.

Low Frequency Algorithmic Trading: What my educational journey is going to focus on. For low frequency algorithmic trading, the trader is the edge, further demonstrated by the trading model(s) and strategy or strategies he/she designs, executes and manages.

Taking a step back, if algorithmic trading is computerized via programs and preset conditions, what main kinds of algo trading logic are out there?

Types of Algorithmic Trading Logic

  1. Macroeconomic news (Examples: Non-Farm payroll reports, FOMC policy minutes, Federal Reserve Beige Book releases)
  2. Fundamental analysis of a financial instrument (Revenues, Earnings releases, Inventory reports, Supply/demand reports, Free cash flow)
  3. Statistical Functions (Correlation, Cointegration, Mean reversion)
  4. Technical analysis of a financial instrument (Moving averages, Stochastics, Relative Strength Index (RSI))
  5. Market Microstructure (Order flow, volume profiling)

3 Core Values for Developing Algorithmic Trading Robots

  1. Market-Prudency (Ideas are fundamentally sound from a market and economic perspective.)
  2. Mathematical Models (Models and strategies are based on sound statistical methods, to maintain positive expectancy and reduce risk of drawdown and loss of capital as able.)
  3. Low Frequency (Trading frequency of the models and strategies are less than one trade per minute. Thus, the strategies do not depend on the speed or computing capacity of computer hardware and/or software.)

Goal: design and execute algorithmic trading models (robots/strategies) using basic software and hardware that average retail traders can afford.

3 Crucial Tranches of Knowledge for Successful Algorithmic Trading

  1. Tools (Trading software platforms, Brokers and execution infrastructure, Coding languages and skills, Performance analysis)
  2. Data (How to access sources, manage, cleanse and use data for algorithmic trading strategies.)
  3. Design Theory (Combining statistics, finance into focused tasks of product development and management.)

Starting Tools

As I start out on this educational journey, I’ll definitely be using the tools below, of which I have no commercial interest in. There may be more tools down the road, but that’s it for now.

  • MetaTrader 4
    • MQL4 language
  • Microsoft Excel
A scenic shot of Palo Duro Canyon from a hiking trail.

Starting SQL from the Absolute Beginning

What is this?

To be honest, I haven’t done much blogging or writing lately. Life (read: work) happens, and it gets busy. However, I’m feeling invigorated with a strong desire to expand my horizons. One such manifestation (snooze, I know) of this is to learn a bunch of new things, for where technology and industry are headed.

Thus, I present to you, learnings on SQL from a course I’m taking! Frankly, this should be boring to a majority of people. However, this is a way of self-accountability, and sharing what I learn along the way about Structured Query Language (SQL) programming, and hopefully how it applies to a bunch of awesome things! If you haven’t bounced already, here we go.

Square 0: What are Databases?

As the title/URL describes, I’m starting from an absolute beginning. While I have a range of experience (or lack thereof in some cases) with HTML, CSS, JavaScript, PHP, Regex, etc., I am completely new to SQL. That said, this isn’t square 1, but square 0.

Databases are systems that allow users to organize and store large amounts of data. Databases can have a wide variety of users and use cases. Potential database / SQL users include analysts in marketing, business or sales. Additionally, technically-focused personnel such as data scientists, software engineers and web developers may also use databases for a range of purposes.

Discussing the Transition from Spreadsheets to Databases

Many practitioners in business have some familiarity with a spreadsheet program, such as Microsoft Excel, Google Sheets or Open Office.

Spreadsheets are often useful for a one-time analysis piece or quick charting, particularly with small data set sizes. Additionally, usage of a spreadsheet program ensures that a wide range of folks with varying expertise can access, use and manage the spreadsheet data.

So where do databases come in? Databases are great for ensuring data integrity and/or handling large & robust data sets/ Databases are also great for use cases where you need to quickly merge different data sets or automate actions with your data for frequent usage or re-use. Additionally, databases are widely used in powering websites and other applications.

In summary, the transition from spreadsheet to data comes when you bring massive amounts of data to life! The hope of the educational materials are that institutional knowledge in spreadsheets can be leveraged to help understand and master SQL database usage.

What are some quick hacks to translating spreadsheets to databases? The first is that tabs of a spreadsheet can be set equal in your mind to tables. Within each table is contained a set of rows, and a set of columns.

What are some of the top SQL database / data warehouse platforms options?

For your enlightenment and education, below are some common SQL database and data warehouse platforms.

  • Postgre SQL (Preferred for example use: free and open source, widely used on the internet, multi-OS)
  • Amazon Redshift data warehouse
  • MariaSQL
  • Microsoft Access
  • Microsoft SQL Server (MS SQL Server Express)
  • MySQL
  • Oracle Database
  • SQLite

 

In addition to straight database programs and data warehouses, there a range of other programs that utilize the SQL programming language for core functions. Below is a short (and rather incomplete list) of other popular SQL applications and uses.

Other Applications of SQL

  • Looker
  • MemSQL
  • Periscope Data
  • Hive (On top of Hadoop)
  • Google BigQuery
  • Facebook Presto

 

A Bit More About SQL

SQL stands for Structured Query Language. It can be applied in a wide range of manners, including PostGreSQL that will be used in these examples. Further, SQL is the programming language that will be used to communicate with databases in this experience.

To start, a sample SQL statement:

SELECT customer_id, first_name, last_name

FROM sales

ORDER BY first_name

 

Review

Here’s a quick overview of what I learned, and have consequently shared.

  • What’s a database?
  • How is a database different from a spreadsheet?
  • Why should a database be used?
  • What are some database / data warehouse platforms that operate with SQL?

If you’re just arriving at this article, here’s my running list of articles that detail how to learn SQL.

Upcoming Educational Material on SQL

  • PostgreSQL Installation
  • Databases and Tables Basics
  • SQL Syntax and Statement Fundamentals
  • GROUP BY Clause

 

  • JOINS
  • Advanced SQL
  • Additional SQL Commands

 

  • Create Databases and Tables through SQL
  • Views
  • PostgreSQL with Python
Exploring marketing career paths by climbing a mountain in Colorado

4 Simple Marketing Career Paths Your Professor Never Told You About

Several months ago, I sat in a warm conference room in the University of Houston Bauer business school. The wind gusted outside;  professors and a couple of brave students shuffled papers on a conference table between presentations. I was privileged to be an advisory board member and mentor at this Enactus UH event.

Unexpectedly (to me), an adjacent student leaned over and asked about marketing career paths. What an intelligent question! I realized determining a marketing career path can be utterly confusing as a student. There are slick advertising agencies, PR boutiques, massive corporations with large silos and vague job titles all around. What on earth is an account executive and what makes it different from an account planner, account manager or anything else?

In that moment, it was my pleasure to share with this student how to evaluate marketing career paths. Some years ago, I was strangely fortunate that by providence I’ve enjoyed such an easy, clear path so far in my young career. Reflecting on that exchange, there are likely thousands of students seeking clarity and guidance on marketing careers.

Below is a simple method that shows you marketing careers at a glance, with detailed explanations below.

marketing-advertising-jobs-career-starts

tldr;

In marketing, you make major two choices that determine your career path: where you work and what you do. You’ll either work at an agency or a client and you’ll either focus on branding or direct response. Obviously, there’s room for a lot of creativity and nuance – but a large portion of marketing careers fit this framework.

Where You Work: Agency

We’ll start here, because I’m most familiar with this area. An agency is a 3rd party that a brands such as Adidas or GM hires to help with marketing. Agencies exist because they provide expertise and impartiality that brands can’t get in-house.

Marketing and advertising agencies are a great starting point to master tactical execution. You’ll often be on the sharp end of the spear: pulling reports, writing copy and auditing campaigns. It’s often a fast-paced environment. The offices have bright lights, open concept floor plans, and quirky perks.

Working in an agency is fun! However, it’s imperative you do some homework before joining an agency. I’ve been very fortunate to work at agencies that offer great benefits and pay. My agency experiences have been nothing short of phenomenal. Regrettably, not all marketing agencies are that way. Some agencies  can be volatile if clients leave or have long/tough hours in uncertain conditions. That said, it’s your privilege of choice!

Where You Work: Client

If you’re not the agency, you’re likely the client. If you’re on the “client side”, you’re likely working in-house at a brand like Hilton or Neiman Marcus.

Client side marketing can vary a lot, depending on the marketing maturity of your organization. In some organizations, you may be managing agencies, vendors and strategy. In less-developed or smaller companies, marketing may be closer to sales support.

In general, client-side jobs are more stable and evenly paced than agencies. Deadlines are usually more relaxed and the pay is generally higher compared to agencies. However, marketing can be vulnerable in a corporate setting. If your organization falls on hard financial times, accounting and marketing are the first functions to be cut.

Much like agencies, there are great brands and tough situations. Do your homework!

What You Do: Branding

Now you know where you can work in marketing and advertising. The aspiring marketer’s next major decision is what you do.

In marketing, you either focus on consumer actions or consumer perceptions. The art and science of branding focuses on consumer perceptions. Branding is a fascinating discipline. You constantly fight battles in the minds of brands and consumers. You must appear first, as the best.

Branding jobs often have “creative” or “strategist” somewhere in the title; a keen sense of strategy is key to your success. To learn more about branding, you should read the classic, “Positioning” by Al Ries and Jack Trout. A caveat to branding-centric positions: you may lack experience in tactical marketing execution at the expense of creative pursuits. If you’re a strategy maven (which is rare), you can survive without concrete tactical knowledge. If you’re weak in strategy, it can be tough to advance your career in this field.

What You Do: Direct Response

The other major marketing avenue is direct response. Instead of focusing on consumer perceptions (branding), you work with consumer actions. Direct response is self-evident, you work to produce an action from a consumer. If you envision a purchase funnel, direct response typically handles the end: making a sale, strengthening a relationship.

Direct response jobs often have “analyst”, “planner” or “specialist” in the title. Working in direct response is fantastic to build practical, tactical experience that can be used in many places & many ways. You’ll find skills such as auditing, research and optimization highly marketable.

The upside of starting in direct response is that marketing is inherently self-promotional: the barriers to entry are low. However, if you don’t chase strategy skills, you can be quickly stuck as a tactician and encounter long-term difficulty advancing your career.

Finding Your Path

The good news: marketing career paths are an open playing field! The bad news: marketing career paths are an open playing field. Hopefully this post brings some definition to a broad field, while pointing out benefits and risks.