Entity Relationship (ER) Diagrams

An entity-relationship (ER) diagram is a graphical representation of entities and their relationships to each other. It is typically used in computing in regard to the organization of data within databases or information systems.

In general, the boxes represent database tables (an entity) and the ovals represent fields in that table (attributes of the entity).

 Simple ER diagram modeling students and courses they are enrolled in

 Complex ER diagram modeling an airline reservation system

Marginal Cost and Marginal Revenue

Marginal cost is the increase or decrease in total production cost if output is increased by one more unit. The formula to obtain the marginal cost is change in costs divided by change in quantity:

Marginal revenue is defined as the "increase in gross revenue by selling one additional unit of output".

If the price you charge (marginal revenue) per unit is greater than the marginal cost of producing one more unit, then you should produce that unit.

Above you can see as Avg Cost (AC2) dips, Price (P2) drops and Output (Q) goes up. As Demand (AR) stays the same, the result is a lower price and higher output

Reference: https://www.khanacademy.org/economics-finance-domain/microeconomics/firm-economic-profit/average-costs-margin-rev/v/marginal-revenue-and-marginal-cost

Securities Valuation and RFR

Securities Valuation: "The process of determining how much a security is worth. Security valuation is highly subjective, but it is easiest when one is considering the value of tangible assets, level of debt, and other quantifiable data of the company issuing a security."

RFR: "the "Risk-Free Rate" is the rate of return of a hypothetical investment with no risk of financial loss, over a given period of time. Since the risk-free rate can be obtained with no risk, any other investment having some risk will have to have a higher rate of return in order to induce any investors to hold it."

Unlike a typical bond loan that has a set period of payments, stock valuation is difficult to calculate because some of the components that are used to calculate the intrinsic value (or "net present value") are unknown.

The challenges of stock valuation, the NPV formula, and a very basic example:

The general formula for NPV (net present value based on future expected cash flows) is:

...where r is the expected rate of return, Div is the expected dividend and P is stock price.

We can find an accurate valuation if we have sufficient information to project into the future:

You can do the calculations longhand but it is much more efficient to use a calculator to do the math for you. Using a Financial Calculator, you can solve for any of the variables in the NPV equation (find the future value (FV), find the number of payment periods (N), find the expected return (INT), etc.). Here is a link to finding these values with a Texas Instruments TI-84 calculator: http://www.tvmcalcs.com/calculators/ti84/ti84_page3

Reference: https://www.investopedia.com/terms/d/dividendgrowthrate.asp

SQL, XML, and XSLT with SSIS

A common need in any business is the exchange of data through various file formats. EDI (Electronic Data Interchange) is typically defined in a document that outlines the positions, symbols, and range of acceptable values for a given file standard (834, 872, etc.).

Example of an 834 EDI formatted file

In this exercise, we are going to very quickly turn a SQL dataset result into an XML file and apply an XSL transformation to it- all through SSIS. Using the techniques described below you will be able to create a process to transform data and write data files for any EDI standard with relative ease.

One can add .NET and other customizable code via SSIS "Execute Process" and "Script Task", however in this exercise we are going to stick to the core problem of fetching data from a SQL Server, transforming that data and then writing the result to an output/destination file.

The SSIS package design

To start, create an Integration Services project in Visual Studio 2017 (install SQL Server Data Tools if your machine does not have the Integration Services Visual Studio project template).

Our project will consist of a Data Flow Task to gather our data (SQL Server to a text file containing the results as XML), and an XML Task which will apply an XSL transformation (XSLT) and write the file to disk.

Data Flow Step #1 -  Source

Data Flow Step #1 - Destination

In my example here, we are using ADO.NET to connect to SQL Server for source data as XML and then writing that data to result.txt.

result.txt (formatted to more clearly see the Customer XML elements

Next, the SSIS XML Task takes result.txt and applies an XSL stylesheet transformation to it, thereby changing the data from XML to HTML with dynamically added CustomerID values (the integer after the static text, "***CUST**ID**").

The key to the particular (CustomerID) transformation we are doing here is the following code which selects each <Customer> in the XML and writes the <CustomerID> value after "CUST**ID**".

<xsl:for-each select="Root/Customer">
 <tr>
  <td>NM1*IL*1***CUST**ID**<xsl:value-of select="CustomerID"/>****MI*YYX123456789!</td>
 </tr>
</xsl:for-each>

XML Task

XSLT is the operation, result.txt is our source data, finally.htm our destination and the XSL is Direct Input shown in the highlighted text above.

You can easily send mail on success or failure to keep the appropriate personnel informed of ETL job/service status. You can use a connected SMTP server to send mail via the SSIS Send Mail Task or (as I have in this example) you can use this T-SQL for sending mail as an Execute SQL Task:

T-SQL for sending email in Execute SQL Task

If you need to create a SQL Server Database Mail account/profile for Gmail, etc, follow this walkthrough.

And with a little luck and no missed steps, you will execute the SSIS package successfully and can go on to really leverage the powerful capabilities of SSIS to build simple and effective solutions for data interchange.

The completed SSIS package and the .htm result of our ETL exercise

Time Value of Money

"Time value of money is one of the most basic fundamentals in all of finance. The underlying principle is that a dollar in your hand today is worth more than a dollar you will receive in the future because a dollar in hand today can be invested to turn into more money in the future."

The basic formula for the time value of money is as follows:

PV = FV ÷ (1+I)^N, where:

PV is the present value
FV is the future value
I is the required return

N is the number of time periods before receiving the money

Reference: https://www.fool.com/knowledge-center/time-value-of-money.aspx

Git Flow

Git or "directed acyclic graph" branch-based development is the maintaining of source code version history not just by file alone, but by a cryptographically unique snapshot of every file in your currently selected "working branch" at a point in time (commit).

This style of team development allows for the development of features that are isolated for easy plug-in/roll-back and integration of snapshots (commits) via merging of branches.

Developers need to beware of stepping on each other's toes (or working on the same files) as this can lead to merge conflicts.

For small business, corporate enterprise and all manner of sprawling code bases, Git is the best tool currently (March 2018) available to help manage the continuous change going on within your application source code.

Statistical Variance and Standard Deviation

Variance and standard deviation are measures of how spread out a distribution of values is. In other words, they are measures of variability within a population (all possible values) or a within a sample (a subset of the population of sufficient size to warrant statistical analysis). Standard deviation and variance, in conjunction with other statistical methods (ANOVA, F-test, T-test, etc.), are used in data analysis models to determine whether, within a sample or population, certain values or correlation between one or more values are statistically significant.

The important distinction: a standard deviation is expressed in the same units as the mean is, whereas the variance is expressed in squared units

Reference: https://stats.stackexchange.com/questions/35123/whats-the-difference-between-variance-and-standard-deviation

Functional Programming

"pass functionality as an argument to another method, such as what action should be taken when someone clicks a button. Lambda expressions enable you to do this, to treat functionality as method argument, or code as data."

Reference: https://nunoalexandre.com/2016/09/16/love-the-lambda

Loan Amortization

What is Amortization?

Webster's dictionary defines amortization as "the systematic repayment of a debt." There are two general uses to amortization: paying off a loan over time, or spreading the cost of an expensive and long-life item over many periods.

*Depreciation: the amortization of tangible assets

Reference: http://www.calculator.net/amortization-calculator.html

Where can we find the data?

There are myriad resources to get data from a variety of domains (insurance, population, health, geography, weather, sports, etc.). You can get the data in .csv form or through API calls.

Here are a few to jump-start your data-oriented application:

Financial: https://fred.stlouisfed.org/search?st=csv

Baseball: http://www.baseball-databank.org/

Soccer: https://www.soccer24.com/team/apia-tigers/84SKUunL/

Government: https://www.data.gov/

Public: https://github.com/awesomedata/awesome-public-datasets

GIS: https://gisgeography.com/best-free-gis-data-sources-raster-vector/

Google Maps API: https://developers.google.com/maps/documentation/directions/


Reference: https://www.quora.com/What-is-the-best-financial-data-source-in-CSV-file-format

Patterns Simpl: Facade

Simplified interface to the overall functionality of a complex subsystem.

For example, a mortgage application is the facade to the subsystem of more complex entities: credit, bank and loan. The .NET Framework is an interface to the more complicated subsystems that lie beneath System.IO, System.Data, etc.

Reference: http://www.dofactory.com/net/facade-design-pattern

Patterns Simpl: State Machine

State Machine is simply an object design that keeps track of the various states that an object can assume. For example:

Reference: http://www.dofactory.com

Patterns Simpl: Template Method

This pattern simply descibes the design of an interface or base class (if implementation details common to all child objects). The base interface or abstract/virtual class is the "Template".