SQL in Financial Engineering (Optimize data handing in VBA/R/MATLAB)

SQL in Financial Engineering (Optimize data handing in VBA/R/MATLAB)

http://learnpythondatasciencenyc.site/

http://bigdatascienceblockchainnyc.site/

http://ebscorp.us/

Please write 10 main points about the course.

1.      Sensitization to Data Analytics and trends. Requires no knowledge of programming or database.

2.      How to make queries your own setup of SQL Database and other scripts. Introduction to calling SQL from VBA, MATLAB and R

3.      Playing with missing data is the most important things and for that I will show five important commands. Missing data types: NaN, blank, 0 and how they are used.

4.      Contains right blend of learning and practice (Ratio 6:4). Highly flexible and tailored as per needs of individual based on his preferred choice of investment theme

5.      The more your reduce data before pulling the easier it would be do the computation. Utility functions for data cleaning, data ready for charting, avoiding looping, error handling will be explained.

6.      Essential Aggregate command, sub-TABLE, VIEWS and PIVOT. Exploring applications in Equity and CMBS (for linking all properties linked) Fixed Income Analytics.

7.      Optional: Introduction to Regression, clustering, Charting, Monte Carlo Simulation, Map Objects for Financial Modelling

8.      Optional Bonus: Essential SQL Queries – Linking SQL with Excel using VBA

Explain 3 main points on how this course will benefit the student?

1.      Getting ready for the next data revolution in Analytical SQL.

2.      Avoiding commands that will slow down SQL. Understand the basics of all major languages used for data handling in SQL, SAS and MATLAB.

3.       Understanding the importance of handling missing data, optimization of speed using novel methods.

Class Number	Topic	Duration
1	Introduction to SQL. Saving, organizing and reading simple data.	0.5 Hours
2	SQL applications in Equity, Fixed Income, Risk [with some reference to other tools like SAS, Excel, MATLAB]	0.5 Hours
3	What and what not to do in SQL. Loops, Logics, Datatypes. How to make data handling faster	0.5 Hours
4	SQL aiding in Quantitative computations: Making data ready for Quant Tech like Regression, Charts, Clustering & Monte Carlo Simulation in Python	0.5 Hours
5	Project on SQL: SQL for structured Data Analysis like Joins, nested queries	0.5 Hours
6	Inner, Outer, Cross Joins and Self Joins	0.5 Hours
7	Sorting Data: Filtering Data with a WHERE Clause. Filtering with the TOP and OFFSET-FETCH Options	0.5 Hours
8	Using Aggregate Functions like GROUP BY Clause, and HAVING	0.5 Hours
9	Using Set Operators, Writing Queries with the UNION Operator, Using EXCEPT and INTERSECT, and Using APPLY	0.5 Hours
10	Pivoting and Grouping Sets: Writing Queries with PIVOT and UNPIVOT and Working with Grouping Sets	0.5 Hours
11	Writing Queries with Built-In Functions and  Using Conversion Functions Using Logical Functions and Using Functions to Work with NULL	0.5 Hours

Project on Big Data using Mongol DB Pipelining, Group by, Map reduce, brackets, inverted commas, etc

Project on Big Data using Mongol DB Pipelining, Group by, Map reduce, brackets, inverted commas, etc

Quant Methods like Regression, Charts, Clustering & Monte Carlo Simulation in Python

Quant Methods like Regression, Charts, Clustering & Monte Carlo Simulation in Python

Data Manipulation in R using dlply library

Data Manipulation in R using dlply library


This is the most important library.


The other libraries are data.table

Installing and Connecting MongolDB

Installing and Connecting MongolDB

Arranging Top Commands on MongoDB

How to use pipeline?
Using group by and other commands in MongoDB.

How to use and and or with MongoDB?


Using where equivalent in MongoDB?


Breaking big data into smaller while we use Python?

Map Reduce functions to handle big data and making it smaller before pulling it.

Introduction to Python for Panda

Libraries that are generally used in python are:

import pymongo
import io,json
import csv
import time
from pymongo import MongoClient
import dateutil.tz
import pandas as pd
import numpy as np
from bson import Binary, Code
from bson.json_util import dumps
import re
from bson.objectid import ObjectId
import string
import matplotlib.pyplot as plt
from pandas.io.json import json_normalize
from datetime import date
import sys
import unicodedata
from bson.objectid import ObjectId
import statistics as st
from functools import partial
import time
import datetime
from datetime import datetime as dt
from datetime import date
import matplotlib.pyplot as plt
import xlsxwriter
import matplotlib.pyplot as plt
from pylab import *


The next part is utility funtions.

Either you can built it into another library or you can include them in the starting of the code.

# Convert dates into fractions

def toYearFraction(date):
    try:
            def sinceEpoch(date): # returns seconds since epoch
                return time.mktime(date.timetuple())
            s = sinceEpoch

            year = date.year
            startOfThisYear = dt(year=year, month=1, day=1)
            startOfNextYear = dt(year=year+1, month=1, day=1)

            yearElapsed = s(date) - s(startOfThisYear)
            yearDuration = s(startOfNextYear) - s(startOfThisYear)
            fraction = yearElapsed/yearDuration

            return date.year + fraction
   
        except:
        return None



Once the utility function ends, then the code starts which is more or less the play with data.




Top 10 Panda plays.

pd.column1.str.contains('searchingtext').sum()
searching string in a column and counting the number of times it is encountered

pd.to_datetime(Jobs['RawDate'], errors='coerce')
This command will force all errors to date format so that we are not in trouble later.

pd['column1'].apply(function)
this will apply function to each value in the column

.sub(value1)
to subtract this value from everything 

.apply(abs)
take absolute values of each entry

value from each column with the respective values from other columns

(df['col1']).sub(df['col2'])


Using greater than or smaller than, renaming columns and using groupby
df= df[(df['col1']<1) & (df['col2'])>0]
df.rename(columns={'col1': 'col1new'}, inplace=True)
df= df[['PropertyId', 'colnew'] ].groupby(['Id']).count()


 Aggregating everything and then summing - mean - etc

df= df[['id', 'col1']].groupby(['id']).agg(['mean','count','sum'])
# check if the sum and the average is correct from the excel file when do you do these
df.rename(columns={'mean': 'Avg','count': 'Countelements','sum': 'Totalforme'}, inplace=True)


List=[long list, separated by , commas]
num=0
end=len(List)
for  num in range(0,end):
    #print List[num]
    Finalpd= pd.merge(Properties, List[num], how = 'left', left_on = '_id', right_on='Id',right_index=True)
    num = num+1

This is how we can use several joins using loops.


def connect_to_MongoDB(env):
    ip_config
    return ip_config.loc[env,'IP']


def get_value1(key,entity):
    print entity
    try:
        res = entity[0].get(key)

    except:    res = None
    return res

def get_value(key,entity):
    #print entity
    try:
        res = entity.get(key)
    except:    res = None
    return res


def isnumber(num):
    try:
        print 'num',num
        print 'num.isdigit()',num.isdigit()
        return num.isdigit()
    except:
        return None



Plotting your first graph

plt.hist(temparray, bins=200)
plt.title("Histogram for temparrays")
plt.xlabel("Size ")
plt.ylabel("Frequency")
savefig('filename.png')
plt.clf()