Application of AI in Risk management – Second Bi Term Homework 4 Attached Files: ·  Week4 – K-Means-Cluster-Analysis using Excel.xlsx Â

image1.

Midterm Exam Instructions

Description: Applying the CRoss Industry Standard Process for Data Mining (CRISP-DM) framework

Review the following case studies found here:

https://www.knowledgehut.com/blog/data-science/top-data-science-case-studies

Choose one of the industries and create an APA formatted white paper by following these steps:

Step 1. [
BUSINESS UNDERSTANDING] Provide additional research and thoroughly outline the problem faced by the industry chosen. (
1-page minimum)

Step 2. [
DATA DISCOVERY/UNDERSTANDING] Locate data (e.g., Kaggle.com or similar) relevant to your chosen industry and produce a model (Use one of the tools RStudio, Python, Jupyter, RapidMiner, or Tableau) that you propose will help resolve the issues you discovered in your research and readings. (
Minimum 1 graphic that adequately depicts your model)

Step 3. [
POSTURING STATEMENT] Articulate what your proposed model is postured to accomplish. (
1-page minimum)

image1.png

Midterm Exam Instructions

Description: Applying the CRoss Industry Standard Process for Data Mining (CRISP-DM) framework

Review the following case studies found here:

https://www.knowledgehut.com/blog/data-science/top-data-science-case-studies

Choose one of the industries and create an APA formatted white paper by following these steps:

Step 1. [
BUSINESS UNDERSTANDING] Provide additional research and thoroughly outline the problem faced by the industry chosen. (
1-page minimum)

Step 2. [
DATA DISCOVERY/UNDERSTANDING] Locate data (e.g., Kaggle.com or similar) relevant to your chosen industry and produce a model (Use one of the tools RStudio, Python, Jupyter, RapidMiner, or Tableau) that you propose will help resolve the issues you discovered in your research and readings. (
Minimum 1 graphic that adequately depicts your model)

Step 3. [
POSTURING STATEMENT] Articulate what your proposed model is postured to accomplish. (
1-page minimum)

image1.png

Sheet1

		Var A	Var B
	Student	GPA	Motovation		Correl =	0.641486711		0.641486711
	Joe	2	50
	Lisa	2	48
	Mary	2	100
	Sam	2	12																									Height CM	Weight Kg
	Deana	2.3	34																									110	44
	Sarah	2.6	30																									116	31
	Jennifer	2.6	78																									124	43
	Gregory	3	87																									129	45
	Thomas	3.1	84																									131	56
	Cindy	3.2	75																									138	79
	Martha	3.6	83																				Calculation for VaR < BADM 566 Application of AI in Risk Management Homework 4 Due: Name: ________________________ Score: _________________ Question 1. Describe clustering, classifications and associations. Explain how clustering is different from classifications. Question 2. a. Describe various risk measurement approaches. b. Describe VaR approaches used to estimate the value at risk. Question 3. Calculate Expected Return in the attached Excel sheet “Week4- HSBC_Expected_Returns_Risk_Analysis”. Please use ExpectedReturn.HSBC, and ExpectedReturn.MK columns. Please see completed example the Excel example attached “Week4 – BARC_StockandMarket_Risk_Returns_Sample”. Question 4. Calculate Standard deviation, Market Beta, and Historical VaR in the attached Excel sheet “Week4- HSBC_Expected_Returns_Risk_Analysis”. Please use ExpectedReturn.HSBC, and ExpectedReturn.MK columns. Please see completed example the Excel example attached “Week4 – BARC_StockandMarket_Risk_Returns_Sample”. Question 5. Calculate the Pearson Coefficient of Correlation between sales and Payroll cost using MS Excel function “CORREL”. Please see the example in attached Excel “Week4 – Correlation Examples”. Date Close Sorted Expected returns Mean 0.000363891 VaR(90) -0.031376644 1/2/20 94.900497 1/3/20 93.748497 -0.01213903 Standard deviation 0.024767271 VaR(95) -0.040374645 1/6/20 95.143997 0.014885572 1/7/20 95.343002 0.002091619 VaR(99) -0.057253397 1/8/20 94.598503 -0.007808638 1/9/20 95.052498 0.004799177 1/10/20 94.157997 -0.0094106 1/13/20 94.565002 0.004322575 1/14/20 93.472 -0.011558208 1/15/20 93.100998 -0.003969124 1/16/20 93.897003 0.008549908 nth Percentile Value = μ + z*σ 1/17/20 93.236 -0.00703966 1/21/20 94.599998 0.014629521 μ: Population Mean 1/22/20 94.373001 -0.002399546 z: z-score from z table that corresponds to percentile value Use Excel Command: NORMINV(1-(percentile/100),0,1) 1/23/20 94.228996 -0.001525913 σ: Standard deviation 1/24/20 93.082001 -0.012172421 1/27/20 91.417 -0.017887465 VaR(90) 1/28/20 92.662498 0.013624359 1/29/20 92.900002 0.002563108 Mean (μ) = 0.000363891 1/30/20 93.533997 0.006824489 Standard deviation = 0.024767271 Z Value VaR(90) 1/31/20 100.435997 0.073791351 Z-value at 90% = 1- 0.90 = 0.1 -1.2816 -0.0313778435 2/3/20 100.209999 -0.002250169 2/4/20 102.483498 0.022687347 VaR(95) Z Value VaR(95) 2/5/20 101.9935 -0.004781238 Z-value at 95% 1- 0.95 = 0.05 -1.6449 -0.0403757931 Sales Payroll cost 6 4 8 4 9 6 5 4 4.5 3 9.5 6 Title Real Statistics Using Excel K-Means Cluster Analysis Kmeans K-means Algorithm X Y Cluster Centroid 1 2 Dist-sq 1 2 Cluster 5 0 1 X 2.6 3.2 7.72 7.72 12.24 1 5 2 2 Y 1.4 3 4.24 6.12 4.24 2 3 1 1 0.32 0.32 4.04 1 0 4 2 SSE 36.92 11.24 13.52 11.24 2 2 1 1 0.52 0.52 5.44 1 4 2 2 Converge FALSE 1.64 2.32 1.64 2 2 2 1 0.72 0.72 2.44 1 2 3 2 1.44 2.92 1.44 2 1 3 1 4.84 5.12 4.84 2 5 4 2 4.24 12.52 4.24 2 X Y Cluster Centroid 1 2 Dist-sq 1 2 Cluster 5 0 1 X 3 2.8333333333 5 5 13.6944444444 1 5 2 2 Y 1 3 5 5 5.6944444444 1 3 1 1 0 0 4.0 HSBC_StockandMarket_Risk_Return BARC_StockandMarket_Risk_Return date close.HSBC close.MK ExpectedReturn.HSBC ExpectedReturn.MK 3/4/14 631.700012 6823.799805 3/5/14 622.599976 6775.399902 3/6/14 625.700012 6788.5 3/7/14 619.599976 6712.700195 3/10/14 615.700012 6689.5 3/11/14 616.599976 6685.5 3/12/14 599.200012 6620.899902 3/13/14 597.200012 6553.799805 3/14/14 598.099976 6527.899902 3/17/14 597 6568.399902 3/18/14 597.900024 6605.299805 3/19/14 591.799988 6573.100098 3/20/14 594.200012 6542.399902 3/21/14 605.700012 6557.200195 3/24/14 606.200012 6520.399902 3/25/14 611.700012 6604.899902 3/26/14 611.200012 6605.299805 3/27/14 608.299988 6588.299805 3/28/14 611 6615.600098 3/31/14 607.5 6598.399902 4/1/14 613 6652.600098 4/2/14 611.200012 6659 4/3/14 610.5 6649.100098 4/4/14 612.5 6695.600098 4/7/14 606.200012 6622.799805 4/8/14 605 6590.700195 4/9/14 612.700012 6635.600098 4/10/14 618.700012 6642 4/11/14 617.599976 6561.700195 4/14/14 621.400024 6583.799805 4/15/14 617.900024 6541.600098 4/16/14 616.299988 6584.200195 4/17/14 617.400024 6625.299805 4/22/14 614.900024 6681.799805 4/23/14 612.200012 6674.700195 4/24/14 613 6703 4/25/14 602.5 6685.700195 4/28/14 600.900024 6700.200195 4/29/14 611.099976 6769.899902 4/30/14 604.099976 6780 5/1/14 607.400024 6808.899902 5/2/14 605.700012 6822.399902 date close.jpmc close.MK expectedReturn.jpm expectedReturn.mk 7/14/11 208.628006 5847 7/15/11 206.272003 5843.700195 -1.129284148 -0.0564358645 7/18/11 191.815994 5752.799805 -7.0082264145 -1.5555279526 7/19/11 195.464005 5790 1.9018283741 0.646645047 7/20/11 205.626007 5853.799805 5.1989121987 1.1018964594 7/21/11 221.559998 5899.899902 7.7490154249 0.7875243181 7/22/11 221.328995 5935 -0.1042620519 0.5949270086 7/25/11 211.490997 5925.299805 -4.4449657398 -0.1634405223 7/26/11 211.307007 5929.700195 -0.086996611 0.0742644279 7/27/11 204.147995 5856.600098 -3.3879671581 -1.2327789702 7/28/11 210.520996 5873.200195 3.1217553716 0.2834425558 7/29/11 205.994995 5815.200195 -2.1499048009 -0.9875365742 8/1/11 200.453003 5774.399902 -2.6903527438 -0.7016145899 8/2/11 200.222 5718.399902 -0.1152404786 -0.9697977444 8/3/11 196.296005 5584.5 -1.9608209887 -2.3415624002 8/4/11 181.054001 5393.100098 -7.7648060132 -3.4273417853 8/5/11 171.817001 5247 -5.1017928071 -2.7090188453 8/8/11 162.024994 5069 -5.69909086 -3.3924147132 8/9/11 165.626999 5164.899902 2.2231168853 1.8918899586 8/10/11 151.216995 5007.200195 -8.7002747662 -3.0532964819 8/11/11 164.287994 5162.799805 8.6438690307 3.107517254 8/12/11 172.925003 5320 5.2572368739 3.0448632707 8/15/11 169.369003 5350.600098 -2.0563827892 0.575189812 8/16/11 167.751999 5357.600098 -0.954722512 0.130826447 8/17/11 160.684998 5331.600098 -4.212767086 -0.4852919129 8/18/11 142.257004 509 K-means Cluster Analysis The objective of this algorithm is to partition a data set  S consisting of n-tuples of real numbers into  k  clusters  C1, …,  Ck in an efficient way. For each cluster  Cj, one element  cj is chosen from that cluster called a  centroid. Definition 1: The basic  k-means clustering algorithm is defined as follows: · Step 1: Choose the number of clusters  k · Step 2: Make an initial selection of  k centroids · Step 3: Assign each data element to its nearest centroid (in this way  k clusters are formed one for each centroid, where each cluster consists of all the data elements assigned to that centroid) · Step 4: For each cluster make a new selection of its centroid · Step 5: Go back to step 3, repeating the process until the centroids don’t change (or some other convergence criterion is met) There are various choices available for each step in the process. An alternative version of the algorithm is as follows: · Step 1: Choose the number of clusters  k · Step 2: Make an initial assignment of the data elements to the  k clusters · Step 3: For each cluster select its centroid · Step 4: Based on centroids make a new assignment of data elements to the  k clusters · Step 5: Go back to step 3, repeating the process until the centroids don’t change (or some other convergence criterion is met) Distance There are a number of ways to define the distance between two n-tuples in the data set  S, but we will focus on the Euclidean measure, namely, if  x = ( x1, …,  xn) and y = (y1, …, y n) then the  distance between  x and y is defined by Since minimizing the distance is equivalent to minimizing the square of the distance, we will instead look at  dist2( x, y) = ( dist( x, y))2. If there are  k clusters  C1, …,  Ck with corresponding centroids  c1, …,  ck, then for each data element  x in  S, step 3 of the k-means algorithm consists of finding the value  j which minimizes  dist2( x, cj); i.e. If we don’t require that the centroids belong to