T-SQL and .NET for XSLT

Steps to perform transformation from SQL to XML to XSLT Output data (csv, htm, tab-delim, fixed-width, etc.):

1). Get your SQL data (assuming you are using SQL Server) into XML format via:

 SELECT [OrderId], [PersonId], [OrderDateTime] FROM [Order] FOR XML AUTO, ELEMENTS, ROOT ('Orders');  

XML Result of "FOR XML AUTO, ELEMENTS, ROOT"..

2). Load XSL transform file (OrderXform.xsl) into a new XslCompiledTransform object (transform):

       XslCompiledTransform transform = new XslCompiledTransform();  
       transform.Load("C:\\Xform\\OrderXform.xsl");  

XSL file used to transform the XML to the desired file output (EDI 834 standard, etc)

3). Using a StringWriter (writer), StringReader (inputReader) and XmlReader (inputXmlReader), read the inputReader XML string into inputXmlReader and apply the XSL transformation to the transform object via: transform.Transform(inputXmlReader, args, writer); //(or another override of Transform())

  using (StringWriter writer = new StringWriter())   
     {   
      // Make an XML reader (inputReader) out of the string (input)  
      XmlReader inputXmlReader;   
      using (var inputReader = new StringReader(input))   
      {   
       inputXmlReader = XmlReader.Create(inputReader);   
       // Apply the transformation to XmlReader (inputXmlReader) and write it to StringWriter (writer)   
       transform.Transform(inputXmlReader, null, writer);   
      }      
      // Retrieve the output as string from writer object  
      return writer.GetStringBuilder().ToString();   
     }   

4). Return the result and write the bits (to memory as Label Text for a demo like this, to file, to domain network location, to non-domain customer ftp, etc.).

The result normally gets written to remote SFTP location or posted to API endpoint for processing

Nothing incredibly complex about this, but it seems to frustrate a lot of people in .NET land so I have documented a quick and easy example.

GitHub Source: https://github.com/Radagast27/Simpl_XSLT_dot_net

Tool for XSLT validation: https://www.freeformatter.com/xsl-transformer.html

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://www.w3schools.com/xml/xsl_examples.asp

https://www.c-sharpcorner.com/article/transform-xml-output-of-sql-query-using-for-xml-auto-statement-to-html-using-xsl/

Dynamic SQL

"a programming technique that enables you to build SQL statements dynamically at runtime." -Oracle Docs

Dynamic SQL is a very useful tool but should only be used when absolutely necessary. Having SQL compiled at runtime amplifies the threat of SQL injection and the readability of the SQL code (dynamic SQL SPs result in large double and quadruple quoted red text which can make things much more difficult to read than when reading regular highlighted SQL code).

But when circumstances require you to use dynamic SQL to get the data you need (ie. there is no other way to structure your database environment and/or the "dynamism" can't be done equally as easy or easier using some sort of data adapter like .NET CLR code) dynamic SQL may be your best option.

For more in-depth info on this subject, see this great reference from SQL Server MVP Erland Sommarskog on this subject and the pros and cons of using dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Here is a brief example of dynamic SQL:

 DECLARE @mult bit = 0  
 DECLARE @sqlToExecute varchar(500)  
 DECLARE @srvWest varchar(50) = 'WestZoneSqlSrv08'  
 DECLARE @srvEast varchar(50) = 'EastZoneSqlSrv08'  
 IF @mult = 0  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ALGO.dbo.[Order]'  
 ELSE  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ' + @srvEast + '.ALGO.dbo.[Order] UNION ALL SELECT TOP 1 OrderID, PersonId FROM ' + @srvWest + '.ALGO.dbo.Order'   
 EXEC(@sqlToExecute)  

Corporate Common Stock, Corporate Notes (Bonds), Treasury Bonds, Municipal Bonds and other Securities

All of these financial instruments are essentially different templates for an agreement on the terms of financing deals.

There is a tendency for bonds and stocks to have an inverse yield (earned interest) relationship

A bond purchase/sale is an agreement between the bondholder (creditor) and the bond issuer (debtor) for the distribution of capital to the debtor which will then be repaid (with interest normally) to the creditor in a certain increment of time (2-yr, 10yr, 30-yr, etc.).

A Treasury bond is issued by the U.S. Treasury and backed by the "U.S. Government's full faith and credit". The same holds for foreign state bonds. Municipal bonds are for state, city and other localities. Government agencies issue bonds as well and these are generally regarded as very safe investments because of the high degree of regulation of the creation and sale of these bonds.

There is more to bond markets than just T-bills

A stock purchase/sale is an agreement to own a portion of a company. So companies with outstanding stock or who decide to offer stock for the first time (IPO or "Initial Public Offering") are turning to John Q. Public for needed financing.

Keep in mind corporate stock is not the same as corporate bonds/notes*

In return for the financing, the stockholder receives dividend payments and the ability to earn a profit by selling the stock if its price rises above the original purchase price. Stocks split the company up into “shares”. As a corporation has no individual owner (by design), corporations make natural stock issuers.

Corporate or Private Common Stock is riskier than government-backed bonds due to higher corporate default rates and being less liquid (less quickly convertible to cash). Unlike bonds which expire, stockholders may choose to hold onto stock indefinitely.

Treasury bonds are safer and tend to offer less return; but over time in the U.S., they have been reliable (no defaults)

Key distinction: Stocks offer an ownership stake in a company, while bonds are akin to loans made to a company.

As seen here patience and a successful company can lead to total returns of over 1500%: https://www.cnbc.com/2017/11/28/if-you-put-1000-in-amazon-10-years-ago-heres-what-youd-have-now.html

And imagine if you would have just invested that nest egg money in Netflix back in 2008? 😉

Corporate Note ($5) for The Johnson Company

*Corporate notes are simply corporate bonds; some financial sources assert an ambiguous and inconsistent distinction (that corp bonds have a shorter maturity, that they are issued differently) but essentially, they are the same thing.

Free Cash Flow

Measure of how much extra cash the business will have after it pays for all of its operations and fixed asset purchases.

A realistic example:

Reference: https://www.myaccountingcourse.com/financial-ratios/free-cash-flow

Working with Date and Time in JavaScript

If you have a requirement to get ShortDate (1/11/1900) and AM/PM time down to the second (2:12:27 PM) from a JavaScript Date object; use this Date prototype method "formatMMDDYYYY()" on the Date object:

 Date.prototype.formatMMDDYYYY = function(){  
 return (this.getMonth() + 1) +  
 '/' + this.getDate() +  
 '/' + this.getFullYear();  
 }  
 var time = new Date();  
 console.log(time.formatMMDDYYYY() + ' ' +  
 time.toLocaleString('en-US', { hour: 'numeric', minute: 'numeric', second: 'numeric', hour12: true })  
 );  

Say we want to calculate the number of days between two given dates. We can utilize this .js function:

 function getDays(firstDate,secondDate){  
  var startDay = new Date(firstDate);  
  var endDay = new Date(secondDate);  
  var millisecondsPerDay = 1000 * 60 * 60 * 24;  
  var millisBetween = startDay.getTime() - endDay.getTime();  
  var days = millisBetween / millisecondsPerDay;  
  // Round down.  
  return Math.floor(days);  
 }  
 alert(getDays(Date(), '1/1/2017'));  

Also, although not referenced above because I wanted to show pure/plain .js in the example, there are several .js libraries for Date operations. The most popular of which seems to currently be: https://momentjs.com/

Reference: https://stackoverflow.com/questions/2035699/how-to-convert-a-full-date-to-a-short-date-in-javascript/2035706

Animated X-to-Horizontal Lines (Hamburger) Toggle for UI Menu

Here is what you are looking for: https://codepen.io/radagast27/pen/OYmvmd

Implementing this UI feature is just a matter of wiring up some icon animation and fadeIn()/fadeOut() to the menu items.

 <html>  
 <head>  
 <script  
  src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>  
 <script>  
 $(document).ready(function(){  
      $('#nav-icon').click(function(){  
           $(this).toggleClass('open');  
       if($('#menu').css('opacity') == '0'){  
        $('#menu').css('opacity','1');  
        $('#menu').fadeIn(300).css('display','table');  
       }else{  
        $('#menu').css('opacity','0');  
        $('#menu').fadeOut(300).css('display','none');  
       }  
      });  
 });  
 </script>  
 </head>  
 <style>  
 body{  
  background-color: #000000;  
 }  
 #menu{  
   z-index: 5;  
   width: 40%;  
   height: 40%;  
   background-color: rgba(0,0,0, 0.95);  
   position: fixed;  
   font-family: Arial;  
   font-size: 1.5em;  
   text-align: left;  
   left: 100px;  
   top: 0px;  
   opacity: 0;  
   display: table;  
 }  
 .hidden{  
   display: none;  
   visibility: none;  
 }  
 #menu ul{  
   margin: 0;  
   padding: 0;  
   z-index: 10;  
   width: 40%;  
   height: 40%;  
   display: table-cell;  
   vertical-align: left;  
 }  
 #menu ul li{  
   cursor: pointer;  
   text-decoration: none;  
 }  
 #menu ul li:hover{  
   background-color: cyan;  
   -webkit-transition: .15s ease-in-out;  
   -moz-transition: .15s ease-in-out;  
   -o-transition: .15s ease-in-out;  
   transition: .15s ease-in-out;  
 }  
 #menu ul a{  
   letter-spacing: 5px;  
   text-align: left;  
   margin-left: 0px;  
   color: #fff;  
   list-style: none;  
   padding: 0px;  
   line-height: 75px;  
   padding: 10px 70px;  
   text-decoration: none;  
 }  
 #menu ul a:hover{  
   text-decoration: none;  
   color: #fff ;  
 }  
 #nav-icon {  
   z-index: 20;  
  width: 50px;  
  height: 35px;  
  position: relative;  
  margin: 25px;  
  -webkit-transform: rotate(0deg);  
  -moz-transform: rotate(0deg);  
  -o-transform: rotate(0deg);  
  transform: rotate(0deg);  
  -webkit-transition: .5s ease-in-out;  
  -moz-transition: .5s ease-in-out;  
  -o-transition: .5s ease-in-out;  
  transition: .5s ease-in-out;  
  cursor: pointer;  
 }  
 #nav-icon span {  
  display: block;  
  position: absolute;  
  height: 5px;  
  width: 40px;  
  background: #bada33;  
  opacity: 1;  
  -webkit-transform: rotate(0deg);  
  -moz-transform: rotate(0deg);  
  -o-transform: rotate(0deg);  
  transform: rotate(0deg);  
  -webkit-transition: .25s ease-in-out;  
  -moz-transition: .25s ease-in-out;  
  -o-transition: .25s ease-in-out;  
  transition: .25s ease-in-out;  
 }  
 /* Icon 3 */  
 #nav-icon span:nth-child(1) {  
  top: 0px;  
 }  
 #nav-icon span:nth-child(2),#nav-icon span:nth-child(3) {  
  top: 12px;  
 }  
 #nav-icon span:nth-child(4) {  
  top: 24px;  
 }  
 #nav-icon.open span:nth-child(1) {  
  top: 8px;  
  width: 0%;  
  left: 50%;  
 }  
 #nav-icon.open span:nth-child(2) {  
  -webkit-transform: rotate(45deg);  
  -moz-transform: rotate(45deg);  
  -o-transform: rotate(45deg);  
  transform: rotate(45deg);  
 }  
 #nav-icon.open span:nth-child(3) {  
  -webkit-transform: rotate(-45deg);  
  -moz-transform: rotate(-45deg);  
  -o-transform: rotate(-45deg);  
  transform: rotate(-45deg);  
 }  
 #nav-icon.open span:nth-child(4) {  
  top: 8px;  
  width: 0%;  
  left: 50%;  
 }  
 </style>  
 <body>  
 <header>  
   <div id="topbar"> <!-- top bar -->  
       <div id="nav-icon">  
        <span></span>  
        <span></span>  
        <span></span>  
        <span></span>  
       </div>  
     <div id="menu">  
       <ul>  
         <li><a href="#">Home</a></li>  
         <li><a href="#">About Us</a></li>  
         <li><a href="#">Portfolio</a></li>  
         <li><a href="#">Case Studies</a></li>  
         <li><a href="#">Careers</a></li>  
       </ul>  
     </div>  
   </div>  
 </header>  
 </html>  
 </body>  

Some minor tweaking of the flexible code referenced above can give you a modern .js transition toggle

Reference: https://jsfiddle.net/f19kz640/

WACC (Weighted Average Cost of Capital)

Weighted average cost of capital (WACC) is the average after-tax cost of a company’s various capital sources, including common stock, preferred stock, bonds, and any other long-term debt. A company has two primary sources of financing - debt and equity - and, in simple terms, WACC is the average cost of raising that capital.

In other words, it is the average rate of return (ROR) that is expected by lenders and shareholders who provide capital to a business.

WACC is calculated by multiplying the cost of each capital source (debt and equity) by its relevant weight, and then adding the products together to determine the WACC value as seen in the WACC calculation formula:

For example, a firm's financial data shows the following:

(E) Equity = $8,000
(D) Debt = $2,000
(Re) Equity profit sharing/stockholder payment rate = 12.5%
(Rd) Interest rate on the debt = 6%
(t) Tax rate = 30%

To find WACC, enter the values into the equation and solve:

WACC = [[(8000/10000)  x 0.125] + [(2000/10000) * 0.06 * (1 - 0.3)]]

WACC = [0.1 + .0084] = 0.1084 or 10.84%

And so the weighted average cost of capital for this firm is 10.84%.

Reference: https://www.investopedia.com/ask/answers/063014/what-formula-calculating-weighted-average-cost-capital-wacc.asp

Marketing Principles

Good contemporary summary of how Price Elasticty of Demand works in markets

Price Discrimination: Co's are more competitive (read: charge lower prices) in a market where demand sensitivity to price changes is higher.

"Perfect" substitutes (commodities like milk, chicken, eggs, gas) are less common as marketing differentiates all (ie. via "Oganic", "Californian", "Eco", etc.)

 Modern market segment analysis informs decisions on customer targeting and product/service/price mix.

I was an early adopter of PC technology. My parents would be considered laggards.

Note chasm. Breach past that and you have achieved popular market acceptance.

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