Truncate minute and second timestamp from date value in SQL

Suppose we retrieve a variable 'Valuation_Date' from SQL database and get following number

2012-01-22 00:00:00.000

We can use convert function to truncate minute and second timestamp using the convert function

convert(varchar(8), Valuation_Date, 112)

then we will get following reault:

20120122

Read More

Matlab tip -- finding the indices of the elements of one array in another

Suppose all elements in array1 are contained in array2, now we want to get the indices of array1's elements in array2.

Here is a simple and neat solution, using Matlab's arrayfun function, which applies a function to each element of an array.

array1=[1,6,2,3,7];
array2=[3,6];
arrayfun(@(x)find(array1==x,1),array2)

The output is:
ans =
      4    2

Read More

A beginner's tutorial for R

• Some common commands

# Quit R
q()
q(save="no")
 
# Setting the default working directory
setwd(file="c:\\AnyDirectory\\")
setwd(file="c:/AnyDirectory/")
 
# Get the default working directory
getwd()
 
# See which variables are currently in the workspace
ls()
 
# Remove a list of variables
rm(var1,var2,var3,...)
 
# Remove all objects from workspace
rm(list=ls())
 
# Note: the variables whose names begin with a dot are not listed by ls(), so they are not removed by above command. 
# Instead, you can use following command to remove them
rm(list=ls(all=TRUE))
 
# Load a package
library(PackageName)
 
# Sink R output to a file
sink(FileName)
sink(NULL) # stop the sink



• You can use either single- or double-quote for text

> cat("I said:\"Hello!\"",'then smile.')
I said:"Hello!" then smile.
> cat("This is Eric\'s book,",'and that is Mike\'s toy.')
This is Eric's book, and that is Mike's toy.

Read More

Control Excel chart position with row and column numbers in Matlab

Sometimes we want to align, in Excel spreadsheet, a chart with a cell which, for example, may contain a label for the chart. We can easily drag (with mouse) the chart to make it aligned with the cell, but it may not be so easy to do this by program. The challenge here is that in Excel spreadsheet a cell position is expressed in row and columns, while a chart position is expressed in points. Depending on the screen resolution, the number of points corresponding to one row or column may vary on different computers. When we want to insert a large amount of charts into a sheet by program, exact positioning of these charts will be a headache. This tutorial teaches you how to programmingly obtain and position the chart in terms of row/column numbers.

1. Determine the chart position in terms of rows
A VBA chart Object has properties TopLeftCell and BottomRightCell which return the cells in the upper left and lower right corners of the chart, respectively. For example, following VBA code is used to retrieve the row property of cells pertaining to the chart position:

Dim TopRow As Long
Dim BottomRow As Long
With ActiveSheet.ChartObjects(1)
    TopRow = .TopLeftCell.Row
    BottomRow = .BottomRightCell.Row
End With



2. Determine the cell position in terms of points
We can use the Left and Top properties to determine the distances (of a cell, row or column) to the left and top boundaries of the spreadsheet, in the unit of points. Here is an example using VBA code

Worksheets("Sheet1").Range("A5").Top
Worksheets("Sheet1").Range("B7").Left
Worksheets("Sheet1").Columns(6).Left
Worksheets("Sheet1").Rows(11).Top


Following Matlab code shows how to insert 10 charts into an excel spreadsheet, each having a figure number printed in a cell beside it:

ObjExcel = actxserver('Excel.Application');
TheWorkbook = ObjExcel.Workbooks.Open('example.xls');
DataSheet = TheWorkbook.Sheets.Item('Data');
RowLastChartBottom = 0;
for i = 1 : 10
    RowChartTitle = RowLastChartBottom + 2;
    DataSheet.Range('A', num2str(RowChartTitle)).Value = ['Chart ', num2str(i)];
    %---
    RowChartTop = RowChartTitle + 1;
    PointsChartTop = ChartSheet.Range(['B', num2str(RowChartTop)]).Top;
    ChartObject = ChartSheet.ChartObjects.Add(10, PointsChartTop, 1000, 250);
    ChartObject.Chart.HasTitle = 1;
    ChartObject.Chart.ChartTitle.Text = '......';
    ChartObject.Chart.Axes.Item(1).TickLabelPosition = 'xlTickLabelPositionLow';
    %---
    CurveObject = ChartObject.Chart.SeriesCollection.NewSeries;
    XRange = DataSheet.Range(......);
    YRange = DataSheet.Range(......);
    CurveObject.XValues = XRange;
    CurveObject.Values = YRange;
    CurveObject.ChartType = 'xlLineMarkers';
    CurveObject.MarkerSize = 2;
    CurveObject.Format.Line.Weight = 2;
    %---
    RowLastChartBottom = ChartObject.BottomRightCell.Row;
end

Read More

A latex template for writing an technical article

\documentclass[8pt,onecolumn,letterpaper]{article}
\usepackage[top=0.5in, bottom=0.5in, left=1.0in, right=1.0in]{geometry}
\setlength{\columnseprule}{0.25pt}
\usepackage[utf8x]{inputenc}
\usepackage{ucs}
\usepackage{amsmath}
\usepackage{amsfonts}
\usepackage{amssymb}
\usepackage{bm}

\setlength{\parindent}{0in}
\setlength\parskip{0.1in}
\linespread{1.25}

\usepackage{graphicx}
\usepackage{color}
\usepackage[colorlinks=true, pdfstartview=FitV, linkcolor=blue, citecolor=blue, urlcolor=blue]{hyperref}

%\usepackage{fancyhdr}
%\pagestyle{fancy}
%\renewcommand{\headrulewidth}{0.0pt}
%\fancyhead{}
%\fancyfoot[CF]{\thesection - \thepage}

\author{Quant Lego}
\title{This is an article template}

\begin{document}
\maketitle

\tableofcontents
\pagenumbering{roman}

\newpage
\pagenumbering{arabic}

\section{Introduction}

\section{Methods}

\section{Results}

\section{Discussions}

\section{Conclusions}

\end{document}

Read More

Southeast and Gulf Coast electricity markets

• Unlike regions with independent power grids, power in the Southern states is provided by integrated power companies. Each of these companies is linked to nearby grids and power can be traded between them. However, because these are still regulated markets, there is little opportunity to trade power within the service area of any of these companies.
• The Gulf Coast region is characterized by mild winters and prolonged hot summers. Long-lasting heat waves can start affecting the area inearly spring and may last until late October ==> Seasonal demand is quite different from the northern states --- demand is low most of the winter and peaks for an extended time in the Summer.
• From a precipitation perspective, the southeastern United States has a tropic climate.
• There is a wet season running from November to May, and a dry season running from June to October.
• There is substantial hydroelectrical generation in the Tennessee Valley area that is affected by this rainfall. During the period of high rainfall, the Tennessee Valley can export power to other parts of the Southeast, the Midwest, or the East Coast.
• Coal and natural gas are both marginal fuels for the area. There are ready supplies of both fuels locally --- the Gulf Coast is a major source of natural gas, and the Appalachian Mountains contain abundant coal deposits.

Read More

New England electricity market (ISO-NE)

• There is only a single trading hub in the New England region. The price at thsi hub are based on selected nodes around the Boston area.
• New England has to import fuel for almost all of its power generation requirements.
• The marginal fuel for the region is natural gas. The bulk of this supply comes from the U.S. Gulf Coast and eastern Canada.
• There are efforts to augment this supply by constructing liquefied natural gas terminals in Massachusetts.
• Most of the population in New England is in the southern part of the region: Connecticut and the area around the city of Boston, Massachusetts. In contrast, the northern part of the region is relatively unpopulated.

Read More

New York electricity market (NYISO)

• The New York power grid is self-contained in the state of New York.
• The southeastern part of this region the most heavily concentrated areas of demand for electricity in the country: New York City and Long Island.
• The region is characterized by the flow of low cost power from the northern and western sections of the state into the high demand regions surrounding New York City.
• Most of New York metropolitan area is located on islands ==> (1) there is limited ability to transfer power to those areas from other parts of the region; (2) high reliance on inefficient generators during periods of peak demand. ==> New York City area is one of the most expensive power market in the country.
• To assure reliable service, power plants in the New York metropolitan area are subject to much stricter rules and regulatory requirements than are common in other regions.

Read More

Midwest electricity market (MISO)

• The Midwest ISO coordinates power for the north-central United States
• The eastern part of the MISO region is heavily industrialized.
• The western section of th MISO region is more rural.
• There are several major trading locations in the region. among them Cinergy Hub is the most influential.
• From a climate perspective:
• The middle of the United States has cold winters and hot summers.
• The cold weather usually starts in December, but can arrive in early November and usually lasts through April.
• The summer months, July and August, are hot and humid, but the weather cools off quickly in September.
• Many of the power plants in the region, including a large number of marginal producers, are coal powered ==> Coal prices often dominate the price of power in the Midwest even in high demand periods. This is a unique feature of the area (In most of United States, the power price is set by natural-gas-fired plants).
• Power is often wheeled from the Midwest to adjacent regions when the price of natural gas spikes upward.
• Cool shoulder months (the spring and fall) in the Midwest ==> Power is often wheeled to southern states where hot weather is more common.
• Some hydroelectric power is available from the Great Lakes and Tennessee Valley areas. Depending on the time of the year, power may be imported from either of these two regions.

Read More

Mid-Atlantic electricity market (PJM ISO)

• The Mid-Atlantic region contains many large population centers, like Washington D.C., Baltimore, and Philadelphia, on the eastern seaboard.
• This area formed one of the first independent power grids, the PJM ISO, named after its condtituent states: Pennsylvania, New Jersey, and Maryland.PJM has since expanded to include West Virginia, Delaware, large parts of Ohio, parts on northern Illinois, and Virginia.
• It is one the largest and most liquid electricity markets. A number of active trading hubs are located within PJM.
• Geographically, the PJM service area is very diverse:
• Large reserves of coal native to Pennsylvania and West Virginia serve as the marginal fuel for western part of the PJM service area.
• Power on the East Coast and Virginia is commonly determined by gas-fired power plants.
• Climate:
• The climate in the northern half of the region ranges from cold winters to hot summers.
• The climate in the southern half of the region is warmer --- mild winters and extended hot summers.
• A large number of power plants in the less populated areas of the PJM service area export power into the more populated regions ==> the power lines between these areas are often congested.

Read More

Pacific northwest electricity market

• In most years, the Pacific Northwest sells surplus power into California and the Southwest.
• About two-thirds of all the electricity in this region comes from hydroelectric production ==> the quantity of surplus power depends heavily on precipitation ==> Water flow in this region directly affects the price of power in California.
• Melting snow from the Cascade Mountain range, which runs through central Washington and Oregon, is a major source of river water.
• To the west of the mountains are several major population centers: Seatle, Washington, and Portland, Oregon. The climate in these regions is fairly temperate all year.
• To the east of the mountains is the start of the northern plains state. This area is characterized by cold winters and hot summers.
• Mid Columbia (MIDC): is a delivery hub for a number of hydroelectric plants on the Columbia River.
• California-Oregon Border (COB): is a major switching station in southern Oregon, adjacent to the California border, that interconnects several major transmission lines.

Read More

Desert southwest electricity market

• The desert southwest has moderate winter and prolonged hot summers
• Mush of the year, the area has a surplus of generation capacity and is a major exporter of power to South California.
• Natural gas is the most common marginal fuel due to the abundant natural gas reserves, but coal and hydropower are relatively common too.
• The area also receives a great deal of sunlight, making it an attractive location for solar power.
• There are three major hubs for electricity trading in the area, and major power generation facilities are located near each hub.
• Mead: is located on the outskirts of Las Vegas. Lake Mead is the artificial lake formed when the Hoover Dam was built on the Colorado River. The Hoover Dam is a major source of hydroelectric power and the primary delivery point for power in southern Nevada.
• Palo Verde: is located at a major nuclear power plant and switching yard about 45 miles west of Phoenix, Arizona. Many high power lines to California are located at this switching yard.
• Four Corners: is located in northwestern New Mexico at the interconnection of several major transmission lines.

Read More

California electricity market (CAISO)

• California is a heavily populated state that is divided into two major zones: NP-15 and SP-15.
• Most of the year, the daily temperatures are moderate to warm. However, the dense population and hot summers can cause spikes in the electrical loads due to a demand for air conditioning.
• One of the major transmission lines, Path-15, bisects the state into two approximately equal parts and has given its name to the two major zones: NP-15 and SP-15.
• NP-15 (North Path 15)
• Covers the northern part of the state (San Francisco Bay area), north of Path 15.
• When rainfall or melting snow allows, this region imports low cost electricity from the Columbia River Basin in Oregon.
• For the remainder of the year, natural gas plants, supplied primarily from western Canada, set the price of power in NP-15.
• SP-15 (South Path 15)
• South of Path 15, the pricing zone for most of Southern California.
• A major importer of power from Arizona (natural gas and solar power) and Nevada (hydropower from the Colorado River).
• Natural-gas-fired plants in the south are fed from supplies in the rocky Mountain and West Texas regions.
• ZP-26
• A large but less important pricing zone that has a relatively low population and excess generation capacity.
• It connects to both the NP-15 and SP-15 zones.

Read More