The SASEQUAN Interface Engine

Reading All Financial Ratios Data

The following statements enable you to access the "All Financial Ratios Data" from the source Damodaran Financial Data (Quandl source code = DMDRN) for Google (GOOG) and IBM on an annual basis. The output is shown in FigureĀ 43.2.

options validvarname=any;
title 'Quandl Data: All Financial Ratios for Google and IBM';
LIBNAME myLib sasequan "%sysget(QUANDL)"
   OUTXML=gstart
   AUTOMAP=replace
   MAPREF=MyMap
   XMLMAP="%sysget(QUANDL)gstart.map"
   APIKEY='XXXXXXXXXXXXXXXXXXXX'
   IDLIST='DMDRN/GOOG_ALLFINANCIALRATIOS,DMDRN/IBM_ALLFINANCIALRATIOS'
   START='2003-12-31'
   END='2012-12-31'
   FORMAT=xml
   ;

data all_fin;
   set myLib.gstart ;
run;
proc contents data=all_fin; run;
proc print data=all_fin; run;

Figure 43.2: Quandl Data: All_Fin

Quandl Data: All Financial Ratios for Google and IBM

Obs date Number of
Shares Outstanding_1
3-Year Regression
Beta_1
3-year Standard
Deviation of
S_1
Book Debt to
Capital Ratio_1
Book Value of
Equity_1
Book Value of
Assets_1
Capital Expenditures_1 Cash_1 Cash as Percentage
of Firm Val_1
Cash as Percentage
of Revenues_1
Cash as Percentage
of Total As_1
Change in Non-Cash
Working Cap_1
Correlation
with the Market_1
Current PE Ratio_1 Depreciation_1 Dividend
Yield_1
Dividends_1 Earnings Before
Interest and
T_1
EBIT for Previous
Period_1
Earnings Before
Interest Taxes_1
Effective Tax
Rate_1
Effective Tax
Rate on Income_1
Enterprise Value_1 EV to Invested
Capital Ratio_1
EV to Trailing
Sales Ratio_1
EV to EBIT Ratio_1 EV to EBITDA
Ratio_1
EV To Sales
Ratio_1
Expected Growth
in Earnings
Pe_1
Expected Growth
in Revenues_1
Free Cash Flow
to Firm_1
Firm Value_1 Ratio of Fixed
Assets to Total_1
Forward Earnings
Per Share_1
Forward PE Ratio_1 Growth in
Earnings Per
Share_1
Previous Year
Growth in Revenu_1
Hi-Lo Risk_1 Insider Holdings_1 Institutional
Holdings_1
Ratio of Intangible
Assets to_1
Invested Capital_1 Market Capitalization_1 Market Debt to
Equity Ratio_1
Market Debt to
Capital Ratio_1
Net Income_1 Net Margin_1 Non-Cash Working
Capital_1
Non-Cash Working
Capital as Pe_1
Payout
Ratio_1
Price to Book
Value Ratio_1
PE to Growth
Ratio_1
Pre-Tax Operating
Margin_1
Price to Sales
Ratio_1
Reinvestment
Amount_1
Reinvestment
Rate_1
Revenues_1 Return on Capital_1 Return on Equity_1 Sales General
and Administrati_1
Stock Price_1 Total Debt_1 Trading Volume_1 Trailing 12-month
Revenues_1
Trailing Net
Income_1
Trailing PE
Ratio_1
Trailing Revenues_1 Value Line
Beta_1
EV to Book Value
Ratio_1
Number of Shares
Outstanding_2
3-Year Regression
Beta_2
3-year Standard
Deviation of
S_2
Book Debt to
Capital Ratio_2
Book Value
of Equity_2
Book Value
of Assets_2
Capital Expenditures_2 Cash_2 Cash as Percentage
of Firm Val_2
Cash as Percentage
of Revenues_2
Cash as Percentage
of Total As_2
Change in Non-Cash
Working Cap_2
Correlation
with the Market_2
Current PE Ratio_2 Depreciation_2 Dividend Yield_2 Dividends_2 Earnings Before
Interest and
T_2
EBIT for Previous
Period_2
Earnings Before
Interest Taxes_2
Effective Tax
Rate_2
Effective Tax
Rate on Income_2
Enterprise Value_2 EV to Invested
Capital Ratio_2
EV to Trailing
Sales Ratio_2
EV to EBIT Ratio_2 EV to EBITDA
Ratio_2
EV To Sales
Ratio_2
Expected Growth
in Earnings
Pe_2
Expected Growth
in Revenues_2
Free Cash Flow
to Firm_2
Firm Value_2 Ratio of Fixed
Assets to Total_2
Forward Earnings
Per Share_2
Forward PE Ratio_2 Growth in
Earnings Per
Share_2
Previous Year
Growth in Revenu_2
Hi-Lo Risk_2 Insider Holdings_2 Institutional
Holdings_2
Ratio of Intangible
Assets to_2
Invested Capital_2 Market Capitalization_2 Market Debt
to Equity Ratio_2
Market Debt
to Capital Ratio_2
Net Income_2 Net Margin_2 Non-Cash
Working Capital_2
Non-Cash Working
Capital as Pe_2
Payout Ratio_2 Price to Book
Value Ratio_2
PE to Growth
Ratio_2
Pre-Tax Operating
Margin_2
Price to Sales
Ratio_2
Reinvestment
Amount_2
Reinvestment
Rate_2
Revenues_2 Return on Capital_2 Return on Equity_2 Sales General
and Administrati_2
Stock Price_2 Total Debt_2 Trading Volume_2 Trailing 12-month
Revenues_2
Trailing Net
Income_2
Trailing PE
Ratio_2
Trailing Revenues_2 Value Line
Beta_2
EV to Book Value
Ratio_2
1 2003-12-31 235.0 0.00 0.0000 0.010834 . 871.5 176.8 334.7 0.00635 0.12536 . -4.435 . 499.086 43.9 0 0 615.68 246.27 659.58 0.60000 . 52375.4 155.417 19.6170 85.069 79.4074 35.7292 . . 117.81 52710.1 0.21606 2.11 93.5071 . 2.3358 0.35502 0.6130 0.1144 0.12117 337.0 52703.5 0.000125 0.000125 105.6 0.07204 -5.4 -0.003684 0 97.5629 . 0.42000 35.9530 128.46 0.52164 1465.9 0.73078 0.17524 268.3 197.30 6.6 5627185 2669.9 190.6 276.514 2669.9 0.00 86.5235 1694.5 1.70 0.3289 0.45891 . 104457 4393 7647 0.04083 0.08237 . 253.577 1.03375 21.4948 4701 0.007336 1220.04 14786.83 10352.41 19487.83 0.29989 . 179624.7 4.09644 1.93482 12.1476 9.2173 2.01529 0.115 0.095 10406.83 187271.7 0.14062 5.31 18.4840 0.070 0.1159 0.10163 0.01 0.5458 0.07175 43849 163639.7 0.14441 0.12619 7613 0.08541 6097 0.068405 0.16026 5.8728 1.86911 0.16590 1.83595 -54.42 -0.00526 89131 0.23609 0.27322 22929 98.15 23632 1872600 92838 0.0 . 92838 1.05 3.63663
2 2004-12-31 277.3 0.00 0.0000 0.000648 . 3313.4 319.0 2132.3 0.01691 0.40608 0.64354 143.910 . 310.686 128.5 0 0 1120.07 534.05 1248.57 0.52320 . 123977.0 155.223 23.6102 110.687 99.2951 38.8740 0.635 0.465 199.64 126109.3 0.11435 6.35 65.3323 . 0.8434 0.44223 0.0409 0.4728 0.05852 798.7 126107.4 0.000015 0.000015 405.9 0.12727 222.6 0.069798 0 43.0533 4.89269 0.35121 39.5420 334.41 0.62618 3189.2 0.66865 0.13857 611.6 414.86 1.9 7507209 5251.0 0.0 . 5251.0 0.00 43.0260 1645.6 1.82 0.3107 0.43526 . 109183 4368 10570 0.06860 0.11200 0.09681 -93.490 1.17155 15.1756 4915 0.009735 1316.48 11291.11 7903.21 16206.11 0.30005 . 143520.0 3.40870 1.52069 12.7109 8.8559 1.49045 0.095 0.075 8543.70 154090.0 0.13899 5.33 15.4184 0.060 0.1125 0.15940 0.01 0.5384 0.09366 42104 131163.0 0.17480 0.14879 8643 0.08976 4701 0.048820 0.15232 4.4093 1.59744 0.11726 1.36212 -640.49 -0.08104 96293 0.18771 0.29055 24737 82.18 22927 4456000 94378 8643.0 15.1756 94378 1.10 2.92535
3 2005-12-31 297.2 . . 0.000000 9419.0 10271.8 838.2 8034.2 0.05709 1.30880 0.78216 -114.693 . 92.689 256.8 0 0 2107.30 1433.74 2364.10 0.31963 . 132704.3 95.829 14.2408 62.974 56.1331 21.6180 0.455 0.385 967.04 140738.5 0.09363 11.26 40.8952 . 0.7960 0.21485 0.0160 0.5684 0.02704 1384.8 140738.5 0.000000 0.000000 1518.4 0.24735 221.4 0.036067 0 14.9420 2.03711 0.34329 22.9268 466.71 0.32552 6138.6 1.03534 0.16121 1459.8 460.48 0.0 2531693 9318.6 2119.8 66.392 9318.6 1.05 14.9420 1574.0 1.64 0.1858 0.40620 33098 105748 3842 13686 0.08112 0.15017 0.12942 68.163 0.80000 17.1465 5188 0.012931 1888.80 9376.00 6533.10 14564.00 0.30321 . 155025.9 3.68644 1.73028 16.5343 10.6445 1.70108 0.095 0.070 7810.94 168711.9 0.13008 6.22 15.6190 0.050 -0.0105 0.14284 0.01 0.5624 0.10500 42053 146070.9 0.15500 0.13420 8519 0.09348 4039 0.044319 0.22172 4.4133 1.80489 0.10288 1.60281 -1277.84 -0.19559 91134 0.15535 0.25739 27156 97.15 22641 4432500 89596 3225.9 45.2807 89596 1.05 3.02682
4 2006-12-31 310.3 1.21 0.3342 0.000000 17039.8 18473.4 1902.8 11243.9 0.05155 1.06026 0.60865 -202.355 0.45482 74.173 571.9 0 0 3550.00 2602.68 4121.90 0.26685 . 206877.5 35.694 13.8169 58.275 50.1898 19.5077 0.325 0.285 1474.14 218121.4 0.12966 16.06 40.9091 . 0.6549 0.26197 0.0090 0.6124 0.10242 5795.9 218121.4 0.000000 0.000000 2940.7 0.27730 491.3 0.046328 0 12.8007 2.28226 0.33475 20.5680 1128.55 0.43361 10604.9 0.44906 0.17258 2829.9 657.00 0.0 5350543 14972.8 3889.6 56.078 14972.8 0.95 12.8007 1506.5 1.63 0.1988 0.44312 28506 103234 4362 10657 0.06135 0.11657 0.10323 -146.379 0.76459 16.0391 4983 0.015960 2410.40 12621.00 8923.93 17604.00 0.29293 . 163049.9 4.02274 1.69529 12.9189 9.2621 1.78345 0.130 0.090 9691.31 173706.9 0.13988 7.64 13.2369 0.055 0.0481 0.15550 0.01 0.6406 0.14584 40532 151023.9 0.15019 0.13058 9416 0.10299 2815 0.030791 0.25599 5.2980 1.23377 0.13805 1.65191 -767.38 -0.08599 91424 0.22017 0.33032 26366 101.13 22683 11019149 96178 9860.2 15.3165 96178 0.95 3.39344
5 2007-12-31 313.3 1.32 0.4285 0.000000 22689.7 25335.8 2402.8 14218.6 0.14919 0.85685 0.56121 -269.870 0.28005 22.671 807.7 0 0 5084.40 3766.93 5892.10 0.25912 . 81085.5 9.572 3.8758 15.948 13.7617 4.8864 0.230 0.220 2441.70 95304.1 0.15943 18.87 16.3037 . 0.5434 0.48656 0.0100 0.5995 0.09076 8471.1 95304.1 0.000000 0.000000 4203.7 0.25333 1034.9 0.062366 0 4.2003 0.98572 0.30640 5.7433 1325.23 0.35181 16594.0 0.44468 0.18527 4860.5 307.65 0.0 2831538 20921.2 4804.8 19.835 20921.2 0.75 4.2003 1385.2 1.05 0.2235 0.55337 28470 120431 4630 16146 0.11256 0.16344 0.13407 -336.333 0.64430 10.3835 5201 0.025610 2770.40 15276.00 10983.90 20477.00 0.28097 . 127303.2 2.67455 1.20678 8.3335 6.2169 1.28868 0.145 0.110 11891.24 143449.2 0.12523 9.42 8.9342 0.075 0.1751 0.30649 0.01 0.5995 0.13611 47598 108175.2 0.32608 0.24590 10418 0.10546 4956 0.050169 0.26592 3.7996 0.71610 0.15464 1.09505 -907.33 -0.08261 98786 0.23076 0.36593 26453 84.16 35274 6537607 105490 11859.0 9.1218 105490 0.90 2.25040
6 2008-12-31 315.1 0.88 0.4044 0.000000 28238.9 31767.6 2358.5 15845.8 0.08174 0.72702 0.49880 82.169 0.35608 36.583 1212.2 0 0 6632.00 6004.02 7844.20 0.09469 . 178017.0 14.364 7.8499 26.842 22.6941 8.1676 0.160 0.135 4775.55 193862.8 0.16475 21.52 28.8095 . 0.3058 0.37771 0.0100 0.6210 0.18372 12393.1 193862.8 0.000000 0.000000 5299.3 0.24314 2030.3 0.093152 0 6.8651 2.28642 0.30428 8.8946 1228.47 0.20461 21795.6 0.48446 0.18766 6542.1 619.98 0.0 3434825 22677.7 6034.9 32.124 22677.7 0.90 6.8651 1339.1 0.73 0.2266 0.71587 13465 109524 4171 12907 0.06306 0.12455 0.11785 -382.777 0.63548 13.8453 5450 0.017700 3022.60 15938.00 11760.65 21388.00 0.26210 . 191787.3 5.56163 2.00755 12.0333 8.9671 1.85069 0.105 0.070 13422.43 204694.3 0.13061 10.18 12.8585 0.120 0.0852 0.23806 0.01 0.6032 0.19269 34484 170768.3 0.19867 0.16574 12334 0.11902 4899 0.047274 0.24506 12.6824 1.31860 0.15380 1.64787 -1661.78 -0.14130 103630 0.34105 0.91600 29723 130.90 33926 8080103 95533 13038.0 13.0977 95533 0.90 4.31927
7 2009-12-31 317.8 0.98 0.4180 0.000000 36004.2 40496.8 809.9 24484.8 0.12667 1.03527 0.60461 319.377 0.53828 29.644 1240.0 0 0 8312.20 6466.81 9552.20 0.22201 0.22202 168808.2 14.654 6.1263 20.308 17.6722 7.1376 0.165 0.125 6577.53 193293.0 0.11963 28.41 20.9071 0.845 0.0760 0.18527 0.0100 0.6024 0.14020 11519.4 193293.0 0.000000 0.000000 6520.4 0.27570 1934.7 0.081803 0 5.3686 1.79663 0.35146 8.1729 -110.72 -0.01712 23650.6 0.56138 0.18110 6494.3 593.97 0.0 2764989 27554.8 7936.0 24.356 27554.8 0.90 5.3686 1305.3 0.69 0.2214 0.53423 22755 109022 3447 13974 0.06737 0.14593 0.12818 75.923 0.80000 13.5063 4994 0.018500 3354.47 17013.00 12592.34 22007.00 0.25984 0.25984 193448.6 5.54596 1.97229 11.3706 8.7903 2.02018 0.130 0.065 14063.42 207422.6 0.12993 12.12 12.1089 0.145 -0.0521 0.11964 0.01 0.5886 0.20824 34881 181322.6 0.14394 0.12583 13425 0.14020 3127 0.032655 0.24987 7.9685 1.03895 0.17767 1.89355 -1471.08 -0.11682 95758 0.36101 0.58998 26772 146.76 26100 5406125 98083 14389.0 12.6015 98083 0.85 4.24568
8 2010-12-31 321.3 0.85 0.2957 0.069710 46241.0 57851.0 4018.0 34975.0 0.16869 1.19283 0.60457 12.300 0.76091 23.971 1067.0 0 0 10381.00 8178.05 11448.00 0.21221 0.21221 172359.8 11.700 4.8198 16.603 15.0559 5.8784 0.200 0.170 5214.75 207334.8 0.13412 38.80 16.6469 0.550 0.2261 0.15516 0.0100 0.6309 0.12619 14731.0 203869.8 0.016996 0.016712 8505.0 0.29007 56.0 0.001910 0 4.4089 1.19853 0.35405 6.9530 2963.30 0.36235 29321.0 0.55516 0.18393 8523.0 645.90 3465.0 3018131 35761.0 10075.0 20.235 35761.0 0.90 4.1712 1228.0 0.40 0.1460 0.55263 23172 113452 4185 11651 0.04791 0.11666 0.10270 176.586 0.80000 14.4647 4831 0.017000 3647.45 18150.00 13650.07 22981.00 0.24793 0.24793 231528.6 5.76731 2.17504 12.7564 10.0748 2.31830 0.120 0.090 14119.48 243179.6 0.12425 13.89 13.2383 0.160 0.1086 0.14358 0.01 0.6010 0.25230 40145 214555.6 0.13341 0.11771 14833 0.14852 2681 0.026845 0.24590 9.2593 1.20540 0.18174 2.14835 -469.41 -0.03439 99870 0.34002 0.64013 27863 183.88 28624 5608725 106448 15623.0 13.7333 106448 0.85 4.69495
9 2011-12-31 325.1 1.23 0.3073 0.067400 58145.0 72574.0 3438.0 44626.0 0.18890 1.17730 0.61490 111.130 0.80000 23.830 1396.0 0 0 11742.00 9275.71 13138.00 0.21000 0.21000 191631.0 10.810 4.0300 16.320 14.5900 5.0600 0.155 0.185 7122.58 236257.0 0.13230 39.20 18.0500 0.365 0.3350 0.16370 0.0100 0.8362 0.12300 17723.0 232053.0 0.018100 0.017800 9737.0 0.25690 437.0 0.011500 0 3.9900 1.54000 0.30980 6.1200 2153.13 0.23210 37905.0 0.52340 0.16750 12975.0 707.38 4204.0 1992857 47544.0 10517.0 22.060 47544.0 0.90 3.7900 1163.2 0.63 0.1459 0.60750 20236 116433 4108 11922 0.04790 0.11150 0.10240 -111.350 0.80000 13.7300 4815 0.019000 4129.36 20286.00 15313.70 25101.00 0.24510 0.24510 237147.2 5.98000 2.27000 11.6900 9.4500 2.22000 0.095 0.065 16132.05 249069.2 0.11920 15.07 12.7100 0.165 0.0700 0.08850 0.01 0.5817 0.25430 39634 217749.2 0.14380 0.12570 15855 0.14830 5346 0.050000 0.25400 10.7600 1.45000 0.18970 2.04000 -818.35 -0.05340 106916 0.38640 0.78350 29852 191.55 31320 4551170 104689 16260.0 13.3900 104689 0.85 4.83000



The SASEQUAN interface engine supports the XML format. The XML data that the Quandl website returns are placed in a file named by the OUTXML= option. The XML map that is automatically created is assigned the full pathname specified by the XMLMAP= option, and the fileref that is used for the map assignment is specified by the MAPREF= option. In the example at the beginning of this section, the SASEQUAN engine uses the MAPREF= and XMLMAP= options in the FILENAME statement to assign a filename:

   FILENAME MyMap "U:\quan950\test\gstart.map";

You can use the MAPREF= and XMLMAP= options to control where the map resides, what you name the map, and how you refer to it with a fileref. You can use the OUTXML= option to name your XML data file; it is described in the section SAS OUTXML File. The XML data file is placed in the folder that is designated by physical-name, which is described in the section The LIBNAME libref SASEQUAN Statement. You can refer to your data by using the myLib libref in your SASEQUAN LIBNAME statement. The myLib libref is shown inside the DATA step in the SET statement. The SET statement reads observations from the input data set myLib.gstart and stores them in a SAS data set named All_Fin, as shown in FigureĀ 43.2. You can also use the SAS DATA step to perform further processing and to store the resulting time series in a SAS data set; this process is described in the section SAS Output Data Set.

To specify the list of Quandl data sets that you want to retrieve, use the IDLIST= option. This option accepts a string, enclosed in single quotation marks, that denotes a list of Quandl codes that specify data sets where the time series reside that you select for the resulting SAS data set. The Quandl codes are separated by commas, so valid Quandl codes cannot contain embedded commas or quotes. The All_Fin data set contains a total of 139 variables: the DATE variable and the 138 time series variables. The DMDRN/GOOG_ALLFINANCIALRATIOS data set includes 69 time series that are designated by _1 to indicate first order in the IDLIST= option; and the DMDRN/IBM_ALLFINANCIALRATIOS data set has 69 variables designated by _2 to indicate second order in the IDLIST= option. The observation range for both datasets is controlled by the START= and END= options. The All_Fin data set contains observations that range from December 31, 2003, to December 31, 2012, as specified by the START= and END= options. The frequency of the data is annual, which is the default frequency, because the FREQ= option was not specified.

Note: The "%20" is a special character for URL encoding of blanks. If the Quandl code that you name in the IDLIST= option contains a blank, you must use "%20" wherever the blank appears in the Quandl code. If the Quandl code contains an underscore, then you must use an underscore in the IDLIST= option. The underscore and the blank are not equivalent in the Quandl databases, so make sure that you use "%20" (URL-encoded space) to designate blank characters.