{"id":14,"date":"2018-12-30T20:47:12","date_gmt":"2018-12-30T20:47:12","guid":{"rendered":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/?page_id=14"},"modified":"2019-11-04T22:13:30","modified_gmt":"2019-11-04T22:13:30","slug":"programming-and-data","status":"publish","type":"page","link":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/programming-and-data\/","title":{"rendered":"Programming and Data"},"content":{"rendered":"

[vc_row][vc_column][vc_column_text]<\/p>\n

A Very Few Useful Programming and Data Tips.<\/h2>\n

 <\/p>\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Translating Financial Statement Variables Into COMPUSTAT Data Items<\/h3>\n<\/td>\n<\/tr>\n

Financial Statement Variable<\/td>\nCOMPUSTAT Data Number<\/td>\n<\/tr>\n
Long Term Debt<\/td>\nDATA9<\/td>\n<\/tr>\n
Property Plant and Equipment (PPE): Wrong Sign!<\/td>\nDATA30<\/td>\n<\/tr>\n
Current Portion of Long Term Debt<\/td>\nDATA44<\/td>\n<\/tr>\n
Sale of Property Plant and Equipment. (Does not appear to match values in the 10-K. If you can figure out how to calculate the sale of assets from the available COMPUSTAT data items please let me know.)<\/td>\nDATA107<\/td>\n<\/tr>\n
Sales (Total Revenue)<\/td>\nDATA117<\/td>\n<\/tr>\n
Net Income Before Taxes<\/td>\nDATA122<\/td>\n<\/tr>\n
Depreciation and<\/b> Amortization<\/td>\nDATA125<\/td>\n<\/tr>\n
Cost of Goods Sold minus<\/b> Depreciation and Amortization<\/td>\nDATA131<\/td>\n<\/tr>\n
Selling, General and Administrative Expenses (SG&A)<\/td>\nDATA132<\/td>\n<\/tr>\n
Provision for Income Taxes<\/td>\nDATA135<\/td>\n<\/tr>\n
Notes Payable<\/td>\nDATA206<\/td>\n<\/tr>\n
Standard and Poors (S&P) Bond Rating: Translated from their letter code to a numerical value. See the WRDS online documentation to translate back.<\/td>\nDATA280<\/td>\n<\/tr>\n
Change in Accounts Receivable<\/td>\nDATA302<\/td>\n<\/tr>\n
Change in Inventories<\/td>\nDATA303<\/td>\n<\/tr>\n
Change in Accounts Payable<\/td>\nDATA304<\/td>\n<\/tr>\n
Cash Interest Paid<\/td>\nDATA315<\/td>\n<\/tr>\n
Cash Taxes Paid<\/td>\nDATA317<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n
\n

R Programming Package Hints, Tips and Manual Clarifications<\/h3>\n<\/td>\n<\/tr>\n

\n

Flexclust Package<\/h4>\n<\/td>\n<\/tr>\n

Flexclust is an R package for creating clusters of similar objects based on their characteristics. It has the ability to call a user supplied distance function and use it to calculate the “distance” between objects in a cluster and the cluster center. I put distance in quotes since it does not need to be a physical distance. It could be, for example, the difference in per capita GDP between a country and the rest of the countries in a cluster.You call the distance function through a command that looks like:<\/p>\n

kmFam <- kccaFamily(dist=mydist, cent=colMeans, groupFun = “minSumClusters”).In this example the distance function is mydist. It is not clear from the manual, but this function needs to take as inputs a matrix of values x<\/i> and matrix of centers c<\/i>. The x<\/i> matrix should contain one row per observation. Each row of the centers matrix c<\/i> is a row vector of centers from candidate clusters. The user supplied distance function should take the matrices x<\/i> and c<\/i> and then calculate a distance from each candidate center for each observation in x<\/i> and then return the result. For example, if x<\/i> is a nxk matrix and centers matrix is 2xk then the returned matrix has to be nx2. Since flexclust determines how many rows will be passed in the centers matrix the distance function must be able to handle an arbitrary number of them. Each row in the centers matrix.<\/td>\n<\/tr>\n

\n

Matching Data Across Data Frames: Find the First Date in One After Dates in a Column of Another<\/h4>\n<\/td>\n<\/tr>\n

#You have two data frames. You need to find the largest value of some column<\/span>
\n#in the second data frame less than or equal to each value in the first data frame<\/span>
\n#and do this ID by ID of some sort. For example, the last rating date prior to a<\/span>
\n#trade date bond Cusip by bond Cusip. You then want to record some information from the<\/span>
\n#second data frame. In the example, the bond’s rating. After the code is finished<\/span>
\n#the resulting data frame will NOT be sorted. If you needed it back in original<\/span>
\n#sort order you need to add a line to do that.<\/span>
\n#You need the dplyr library for this to work. In this sample code uses an inner_join<\/span>
\n#so data is only kept if it is in both data frames.<\/span><\/p>\n

library(dplyr)<\/p>\n

#Some sample data, say bond trades.<\/span>
\nb <- data.frame(cusip=c(rep(“A”,30),rep(“B”,10),rep(“C”,5)),td=c(62:91,173:182,266,268,272,279,281),stringsAsFactors=F)<\/p>\n

#More sample data, say bond ratings.<\/span>
\nr <- data.frame(cusip=c(rep(“A”,10),rep(“B”,5),rep(“C”,7)), rd=5*c(12:21,33:37,52:58), rate=c(101:122), stringsAsFactors=F)<\/p>\n

#Create the new data with the columns containing the value in table 2 from the column rate in r that corresponds with the<\/span>
\n#largest value in r from the column rd that is less than or equal to td for each cusip in b.<\/span>
\nvv <- inner_join(b,r) %>% filter(rd<=td) %>% group_by(cusip,td) %>% filter(rd==max(rd))<\/p>\n

#Turn the output back into a data frame and sort by cusip and td.<\/span>
\nvv <- data.frame(vv)
\nvv <- vv[with(vv,order(cusip,td)),]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

[\/vc_column_text][\/vc_column][\/vc_row]<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"

[vc_row][vc_column][vc_column_text] A Very Few Useful Programming and Data Tips.   Translating Financial Statement Variables Into COMPUSTAT Data Items Financial Statement Variable COMPUSTAT Data Number Long Term Debt DATA9 Property Plant and Equipment (PPE): Wrong Sign! DATA30 Current Portion of Long Term Debt DATA44 Sale of Property Plant and Equipment. (Does not appear to match values […]<\/p>\n","protected":false},"author":3,"featured_media":0,"parent":0,"menu_order":4,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"class_list":{"0":"post-14","1":"page","2":"type-page","3":"status-publish","5":"entry"},"_links":{"self":[{"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/pages\/14","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/comments?post=14"}],"version-history":[{"count":5,"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/pages\/14\/revisions"}],"predecessor-version":[{"id":130,"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/pages\/14\/revisions\/130"}],"wp:attachment":[{"href":"https:\/\/faculty.som.yale.edu\/matthewspiegel\/wp-json\/wp\/v2\/media?parent=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}