StockAnalyser NodeJS: Connecting to an Existing MSSQL database (1)

Important:
This blog shows my learning curve in different programming languages. Take my code with a large grain of salt.

The NodeJS script shall connect to an existing MS SQL database. This blog post summarizes some of the initial investigations to make this happen.

The connection string is:

The different components of the connection string are:

• Integrated Security means that I am not using a username/password.
• AttachDbFileName points to the file on the file system
• Data Source refers to the MS SQL Server instance.

First step is to find and control the SQL Server software. Sometimes, it comes with the installation of Microsoft Visual studio. The software SQL Server Data Tools isn't included in my MSVC installation  (2017) and I need to add it using the MSVC installer.

The settings for my local database

Normally, the SQL Server Data Tools provides the server to other applications. For some reason I don't have it so I will install MS SQL Server on top of MSVC in the next blog post.

StockAnalyser: Recovering Missing Data

In this blog post, I'll add code that protects the program when parsing strings to float values and I'll also add code that tries to recover missing information.

First, I'll add try-catch clauses to handle parsing errors from string to floats.

After that, I'll add functions to verify and populate null entries of the key numbers.

For the triplet of P, P/E and E. there are eight possible scenarios:

P = null, P/E = null and E = null - Impossible to recover

P = null, P/E = null and E != null -Impossible to recover

P = null, P/E != null and E = null - Impossible to recover

P = null, P/E != null and E != null - Recover P

P != null, P/E = null and E = null - Impossible to recover

P != null, P/E = null and E = !null - Recover P/E

P != null, P/E != null and E = null - Recover E

P != null, P/E != null and E != null - No need to recover

I'll start to count how many null values I have. If there are two or more null values, I can't recover any data. If there is no null value at all, I don't need to recover any data.

I will add two boolean flags later.
One will indicate if any data in the stock record has been recovered and
 another flag will indicate if the data is so bad that it shouldn't be sent into the database.

The screen shot below illustrates how the earning value is recovered (example from 2018-06-13, AAK, a Swedish company that is producing vegetable oil and fat.)

Similar functions are added for the Price, Price per Capital (P/JEK) and the Capital per share.

Now, only profit margin is missing. Since that is calculated as the profit (P) divided by the total revenue (not recorded), I can't recover that value. I'll simply parse "Null" to the database, if that is missing.

After fixing this, I was able to scan stocks until 2019-02-15. For that date, most of the information is missing.

The fix here is to add a flag that indicates whether the stock record contains sufficient information to be added to the database. 

Side note: The board of AAK decided to perform a 6:1 share split, where one old share generated six new shares. This explains why some data was missing for that date. The next project will detect and handle share splits.

StockAnalyzer: Validating the Raw Data - Broken input data and Missing Report date

My Stock project is progressing and I'm currently adding fixes for flaws in the input data that I want to add to a SQL database. In my previous blog post, I added a fix for records that has no information about the stock price.

In this blog post, I address some more issues:

Input Data Replaced by HTML Tag

The program tries to parse ass="BrodTextWhite" width="12">? to a float number. I don't know why this string has replaced the data.

I will handle this by simply removing the bad record. I haven't seen that for other stocks and there is no information in the string that can recover the missing data.

Invalid Data for Dividend or Report Dates

Another issue that I saw was that some records had an invalid date for the report. I solved that by checking that the string can be parsed to a date. If not, I'll feed the NULL value to the database.

Now, the database has a NULL value when the date is missing.

Empty Profit Margin

Now, I was able to scan three stock records for almost five years (2013-07-28 to 2018-06-13). For AAK, the program is trying to parse an empty string to a value.

There are some empty values: Earnings per share, Capital per share, Dividend per share and Profit Margin. I can recover everything except for the Profit Margin, that requires information about sales in relation to the production costs. Since this information is important, I will add the stock information but set the profit margin to NULL.

Populating Missing Data?

I will create functions for filling in some data that is missing, if corresponding data can help calculating that data.

For the AAK record of 2018-06-13, the Earnings per share is missing.

I will handle this in two steps (next blog post):

First, I'll add try-catch clauses to handle parsing errors from string to floats.

After that, I'll add functions to verify and populate null entries of the key numbers.

StockAnalyzer: Selecting the Input Files

Now, it is time to interpret the input files. They are named in the form "YYYYMMDD.csv" and contain a header row that describes the contents.

I need to consider some possible issues for the input data:

• The file headers has slightly different formats since I have added some information in the later versions. 
• The early versions of StockReader had hard coded references to the stocks. Thus, some stock records has sometimes slightly different names of the stocks. Sometimes, the companies changed names too. I need to add checks for the stock names, and possibly add a second table that maps different names to the same stocks.
• Sometimes, the data acquisition was interrupted.

I'll start by selecting which files to analyze, based on the file names and the time interval that the user has specified in the dateTimePickers.

In order to do that, I use a built in function for removing the file path and the file extension. The remaining string contains the year, month and date:

Now, I process only one file.

Now, I want to check whether the corresponding date is within the time interval. For that purpose, I need to convert the string 20101110 to a datetime format. This is done in three steps:

Step 1: Check the file format - I'm only interested in comma separate value files.
Step 2: Check the date - I'm only interested in files that are corresponding to plausible dates.
Step 3: Convert the date to dateTime format and check whether the date is between the start and end date that the user has specified.
If all these checks are OK, the program will analyze the file. I'll do that in the next blog post.

Special Characters - ÅÄÖ
Since I am collecting data from Swedish stocks, I will handle strings with Swedish characters. I solved that by adding a streamReader object that is configured for the Windows 1252 encoding that contains Swedish characters.

As a side note, I added an UNIQUE constraint to the database. There can be only one record for one company at a particular date.

StockAnalyzer: Adding Code

Now, it is time to add some code to the program.

Database
The database is controlled from dbParser.
I added some code for printing the contents of the database to the console and also code for clearing all data from the database.

Code for printing contents of database to console.

Code for deleting all records of the database.
The first step is to delete all records from the StockTable.
The second step is to set the identity counter to zero.

Scanning for Files
I have saved my input data in a local folder on my computer. Thus, it is convenient for me to have that folder as the default folder. However, I want to avoid assuming that that folder exists - someone might fork my repo and install it on another system.

To handle this, the program will check whether my folder exists. If it doesn't, it will use the user's document folder instead.

I'll clean up this form soon.

When the user presses the "Scan to Database" button, the program will scan for the files in the class scanFolder. First, I need a list of all files in the current folder. Once I have that file list, I will iterate over it and scan only the files that are in the time interval.

In the next blog post, I'll make the program select which files to analyze and also start to add records to the database.

StockAnalyzer: Creating a Structure of the Program

I've been able to populate my database with some dummy data and to read it from the database. That's an important step and now, I need to define the overall structure of the program.

Use Cases identifies some scenarios where an user is using the system:

• User wants to delete all stock records from the database
• User wants to scan all available files and add the data to the database
• User wants to scan all files within a specified time range
• User wants to update the database with the latest results from StockReader

The program will have three different modules:

• User Interface - Collects the parameters and call methods/functions
• FileScanner - Select which csv files to scan and determine whether the data in a row contains enough information so that it can be added to the database.
• DBParser - Build SQL queries and launch them. Database error handling.

Now, I have a rough idea what the program should look like. I have added a couple of user interface widgets to my app. The code is stubbed right now:

I will clean up this form later.

In the "Time Interval to Scan" group box, the user will be able to limit the number of files that shall be loaded into the database.

In the "Directory to Scan" group box, the user will be able to select where the input files are. The text of the button shows the current folder.

The user will be able to clear the database and scan all files that matches the dates in the folder. The algorithm will look like this:

• List all csv files in the current folder. 
• For all files that are matching the time interval:
• Check the headers in order to know how to interpret the data.
• For all rows in the file: 
• If the needed data is there for that stock, add the information to the database

I have also created two classes: FileScanner and DBParser. Those are instantiated when the program starts.

In the next blog post, I'll fill the classes and widgets with code.

StockAnalyzer: Connecting to the Database and Designing It

As I have mentioned before, I will process the data in three steps:

• A C#-app for Windows that will populate the database (ongoing)
• A web app that will analyse the database and look for inconsistencies
• A Python script (or possibly Matlab script) that will perform machine learning on the data set.

In the last blog post, I created the database that I will work with. Now. I'll start populating it.

The first step is to connect to the database. I'll use an example that I found:

The language setting is Swedish.
For non-Swedish speakers, the error message
says that the key attachdbfilename is invalid.

The key attachdbfilename is invalid. The DataDirectory needs to be changed to an actual library. I'll start by fixing the connectionString and using a standard SQL query:

I can now connect to the database and launch a simple query!

The First INSERT and SELECT to the Database

I extended the query to the database. First, I populated the database with some dummy data. After that, I selected all rows in the database and presented them as a message box:

It shouldn't be possible to add more than one record of a particular stock for a particular date. I will add a UNIQUE constraint. I will fix that soon.

Next, I need to plan how to design the application, with different classes and layouts for the user interface.

Side Note:

I added  the project to my GitHub account, using instructions on GitHub.

The updated code is available at https://github.com/cutetrains/StockToDatabase

https://www.youtube.com/watch?v=1EpYqtSlOr8  4:20

StockAnalyzer: Building the First C# Windows App and a Database

It's been a while since I used Visual Studio, so I had to install a lot of updated.

Updating the environment takes time but is crucial for development.
I have spent too much time debugging issues that were solved with an update.

Since my version of Qt is dependent on Visual Studio, I had to rebuild TrafficControl and verify that it still works. Fortunately, it worked like a charm.

To learn about Visual Studio, I opened a simple example app that simply contains a text field. a button and a hyperlink:

The code is very intuitive: The main thread shows the form and the Form1 designer sets up the user interface element. In Form1.cs. there are some listeners to events such as a mouse click or a click to a hyperlink.

Adding a Database and a Data Source
First, I need to create a database. It is possible to use the Azure platform to do that online, but I'll create a local Microsoft SQL database instead. I followed the steps from the documentation.

Step 1: Select a local Database

Step 2: The database model shall be a dataset.

Step 3: Name the database object to StockRecord

Now, there is an empty database that I can use in my project.

The next step is to design the database itself and connect to it.

In the next blog post, I'll connect to the database from the program and populate it with some dummy data.

StockAnalyzer: Applying Machine Learning on Stock Information

After using some neural networks to analyse a small set of data for a friends pet project, I will focus on my own data.

I have a web scraper, StockReader, that collects some information about public stocks that are listed in Sweden. That has been in use for eight years and I have more than 1500 snapshots of a couple of hundreds of stocks.

It was originally written in C++ (the code was terrible, but it was essential for me to learn to build a more complex program) and I later ported that to a twenty-four line python script that is scheduled to run three times a week.

Now, I want to use the data to learn more about machine learning and analysis of time series. I also want to get experience from C#/Visual Studio and Angular JS.

I don't expect to find a magic algorithm  that helps me in stock-picking. The purpose of this project is to learn coding, machine learning, C#, Visual Studio and Angular JS.

The Different Technology Areas for the Project
The data is saved in csv files (comma separated values). The data includes stock price, earnings per share, dividends, profit margin, RSI and date for the quarterly reports.

I will create a Windows program based on C# in Visual Studio to populate a mySQL database with the stock data. It would probably be easier using Python but I want to explore new tools and programming languages.

After the data is in the database, I will build a web app using Angular JS, That program will check the data and search for possible stock splits and inconsistent data.

Once the data is corrected, I will analyse it. Since Python has very powerful AI packages, I will use Python to extend the web app above.

I will start by describing the data and some relevant topics for stock markets.

TrafficControl: Resuming...

When resuming the project, I've seen that the tile issue with tiles that aren't shown seem to be unstable. Some zoom levels work but not others. Qt Blog has addressed that issue and discussed solutions to the issue:
http://blog.qt.io/blog/2017/03/09/provisioning-openstreetmap-providers-in-qtlocation/

I also installed Microsoft Visual Studio, and used that compiler. No missing DLLs and much quicker compilation. I assume the reason is that MSVC is more adopted to the Windows 10 workstation that I'm using than MinGW that I used before.

The next blog post will describe a breakthrough in TrafficControl!

|| AND &&: Why it is a good idea to quibble

I had to recover my Windows 8 installation one week ago, and in that process, I re-installed Qt. This time, I also included the Microsoft Visual Studio Express compiling option for Qt.

After doing that, I tried to rebuild my programs TrafficControl and StockReader. I got houndreds of strange compiler errors that I didn't expect since the programs compiled without warnings before.

The issue was that I used the keywords "and" and "or" in several if clauses. Those keywords are allowed by gcc/g++ (GNU Compiler Collection), but not by Microsoft Visual Studio 2013: http://msdn.microsoft.com/en-us/library/2e6a4at9.aspx

My personal preference would have been to use AND and OR for logical keywords - that would have kept my source code as similar to human language as possible. But I also want my source code to be portable between different operating systems and compilers. So from now on, I'll continue with using the ||, && and != operators instead of OR, AND and NOT.

I still have some problems making static builds for  my programs. More about that in future blog posts.