## Saturday, March 19, 2016

### R code for Breaking X/Y axis

# http://stackoverflow.com/questions/19612348/break-x-axis-in-r
# http://www.inside-r.org/packages/cran/plotrix/docs/axis.break

# Example 1
x <- c(9.45, 8.78, 0.93, 0.47, 0.24, 0.12)
y <- c(10.72, 10.56, 10.35, 10.10, 9.13, 6.72)
z <- c(7.578, 7.456, 6.956, 6.712, 4.832, 3.345)
plot(x, y, col='blue', pch=16, xlab= 'x', ylab='y, z')
points(x, z, col='red', pch=17)
library(plotrix)
axis.break(1,2,style="slash")

#Break X axis

xgap <- ifelse(x > 8, x-6, x)
#Possibly you'd want to check if there are values between 2 and 8.
plot(xgap, y, col='blue', pch=16, xlab= 'x', ylab='y, z', xaxt="n")
points(xgap, z, col='red', pch=17)
xat <- pretty(xgap)
xat <- xat[xat!=2]
xlab <- ifelse(xat>2, xat+6, xat)
axis(1,at=xat, labels=xlab)
library(plotrix)
axis.break(1,2,style="slash")

library(plotrix)
par(bty="n") # deleting the box
gap.plot(x,y, gap=c(2,7.5), gap.axis="x", pch=16,
col="blue", ylim=range(c(y,z)),
xtics=c(0:3,8:10), xticlab=c(0:3,8:10))

gap.plot(x,z, gap=c(2,7.5), gap.axis="x", pch=17,

abline(v=seq(1.99,2.09,.001), col="white")  # hiding vertical lines
axis.break(1,2,style="slash")

# Break Y Axis

ygap <- ifelse(y > 8, y-6, y)
#Possibly you'd want to check if there are values between 2 and 8.
plot(ygap, x, col='blue', pch=16, xlab= 'x', ylab='y, z', yaxt="n")
points(ygap, z, col='red', pch=17)
yat <- pretty(ygap)
yat <- yat[yat!=2]
ylab <- ifelse(yat>2, yat+6, yat)
axis(2,at=yat, labels=ylab)
library(plotrix)
axis.break(2,2,style="slash")

## Tuesday, March 15, 2016

### OLAP Cubes in SQL Server for Adventure Works

OLAP Cubes in SQL Server for Adventure Works

References:

To install Other Databases check out

## Saturday, March 12, 2016

### Importing HTML file in Excel using VBA

This proc opens HTML files in excel, copy it into the sheet and then close the html without saving.
This ways you can import data from various html files into excel.
This can be modified to import values from other format like XML also.
This is a although very mundane way but VBA 6 is very old, VBA.NET might have something better.

For learning more VBA skills please check out our courses at Qcfinance.in

Sub Import()
Dim oExcel As Excel.Application
Dim ws, ws2, ws3 As Worksheet
Dim wb As Variant
Dim wkb As Variant

Set activewkb = ThisWorkbook
Path = ActiveWorkbook.Path

Dim StrFile As String
Path = ActiveWorkbook.Path
' MsgBox Path
StrFile = Dir(Path & "\HTML\")
Do While Len(StrFile) > 0
MsgBox StrFile
StrFile = Dir
' MsgBox StrFile

Set wb = Workbooks.Open(Path & "\HTML\" & StrFile)
On Error Resume Next
'MsgBox ActiveWorkbook.Path

For Each sh In wb.Sheets
sh.Copy after:=activewkb.Sheets(activewkb.Sheets.Count)
Next sh

'MsgBox "The name of the active workbook is " & ActiveWorkbook.Name
wb.Close savechanges:=False

Loop
End Sub

## Monday, March 7, 2016

### Debug Command to print Files

Sub LoopThroughFiles2()
Dim StrFile As String
Path = ActiveWorkbook.Path
StrFile = Dir(Path & "\")
Do While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
Loop
End Sub