Author Archives: Phillip

Principles of Analytic Graphics

Principle 1: Show comparisons: show evidence for a hypothesis is always relative to another hypothesis.

Principle 2: Show relationships: The relationships can be causality, mechanism, explanation, systematic structure. This is the purpose of an analytic graphics.

Principle 3: Show multivariate data: The real world is always complex and multivariate. It’s more likely to reflect the true picture using multivariate data. Otherwise, we assume all other factors not included are the same (or homogeneous) for each group displayed.

Principle 4: Integrate multiple modes of evidence: Each mode of evidence explains one perspective of the relationships. Integration of multiple modes can then better disclose the full spectrum of the relationships.

Principle 5: Describe and document the evidence: A data graphic should tell a complete story that is credible. It helps develop credibility to document a graph with appropriate labels, scales, sources, etc.

Principle 6: Content is king. 

adapted from course note of ‘Exploratory Data Analysis’ (John Hopkins Data Science Specialization course).

excel VBA: Download URL file to local drive

VBA code to download a file from URL address:

Sub DownloadUrlFile()
 
' Create an array to hold the response data.
Dim arrDownloadedBytes() As Byte
Dim WinHttpReq As WinHttpRequest
Dim strURL, strLocalPath, strLocalFileName As String
 
strURL = "https://www.xxxx.com/documents/FilenameToDownload"
 
strLocalPath = "C:\User\documents\"
strLocalFileName = "FilenameDownloaded"
 
Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
WinHttpReq.SetAutoLogonPolicy (0)
 
'In case you have V5.0 instead of v5.1 - use this line
'Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.0")
 
' create the HTTP Request
WinHttpReq.Open "GET", strURL, False
 
' Send the request
WinHttpReq.Send
 
' copy the response body to a local file
Open strLocalPath & strLocalFileName For Binary As #1
arrDownloadedBytes() = WinHttpReq.ResponseBody
Put #1, 1, arrDownloadedBytes()
Close
 
End Sub
 
 

Excel Tip: Convert formulas to values with a single drag

Sometimes we need to convert data driven by formulas to values only. Below is a tip that you can do it with a single drag.

Step 1: Select the data range that you want to convert;
Step 2: Press CONTROL key and move your mouse to the frame of the data range and then press right key of your mouse;
Step 3: Move the data range to the destination and then release the right mouse key;
Step 4: Select ‘Copy Here as Values Only’ from the pop-up menu.

If you want to convert the data in the same location, you just need to move the data range to any new location and move back to the same location before releasing the right mouse key in Step 3.