pmayhew,
you write
Bit I am still hoping to check this on a daily basis; that could get tedious so
I am still looking for a better way to get this data? I could not agree more with you that all such manual operations are
very unproductive. Especially since there are many such macro variables that
require a different special handling each and that have to be downloaded
from the internet.
I cannot program the automatic download in Wealth-Script (only fundtimer
has the ability) but I can try to analyze the problem and to prototype it in R.
I am already downloading thus the NYSE Turnover and SP500 Cash dividends and
Earnings and there are many other useful series that Yahoo does not provide.
It is unthinkable and barbaric to try and obtain them
daily (or even monthly) by manual operations.
Maybe you can give a Wealth-Script version a try and publish it. If fundtimer
feels good that day he may debug it or re-write your program to actually work
correctly.
ANALYSISGenerally speaking one has to:
PRELIMINARY: Make a directory named "MacroIndexes" or something so that you can make
a Wealth-Lab datasource out of files in this directory.
Decide on date format, data formats and file extension, naming convention for
the file,number of columns, frequency (daily, monthly, quarterly?), number
of rows for general information about the file (such as the URL and
the description) and row number were the data that the WL datasource will read
will start.
If you have more than one frequency (daily and monthly for example, create more
such directories, one per frequency.
I would suggest ISO date format ("YYYY-MM-DD"), ".csv" file extension, 1 column of
data and one for the date, leave free rows for information about the series
and that WL will skip and start writing data for WL in a later row.
A mnemonic symbol name for the file, eg "PCRAT_D.csv" for Index Put/Call Ratio
Daily, except of course if a standard symbol name exists, it should be used
instead.
This output directory is were you will place the PUT/CALL ratios, maybe the
volumes and any other macro series of the same frequency.
Create a WL datasource for this directory (use a prototype data file created
with notepad)
Then:
STEPS
STEP 1: Assign the output directory name (which should already exist) to a variable
STEP 2: Assign the output file name (complete path)
STEP 3: Download the file from the url into a temporary directory (there
are lots of free download utilities which you can use by shelling to DOS
from within Wealth-Script, or use COM with Internet Explorer)
STEP 4: Skip step 4 in this case since the PUT/CALL ratio comes in a .csv file. But
if it did not, you have to parse the HTML or the text
(http://www.nirsoft.net/utils/htmlastext.html)
has a nice free utility to convert HTML to text). It is sometimes simpler to parse
the text.
STEP 5: Read the temporary file into a variable
STEP 6: Extract the dates and the columns of interest
STEP 7: format the data for output, example put the dates in ISO format
STEP 8: Prepare five lines of comments and headers (or blanks if there
is nothing to describe the series )
STEP 9: Starting at row 6, write the file(s) to the output directory (The
Wealth-Lab data source directory previously created)
STEP10: Check if WL datasource can read the data
Prototype in R script
#-------------------------------------------------------------------------------
#PURPOSE: Download index Put Call Ratio and volume
#!IG 2008-01-01 1:02:05 PM
#INPUT DATA SOURCE:
# http://www.cboe.com/publish/ScheduledTask/MktData/datahouse/indexPC.csv
#Create the output directory name
outDir <- "G:\\DATA\\MacroData_D\\"
#Create the output file name (complete path)
outFiles <- paste(outDir, c("PCXVOL_D.csv", "PCRX_D.csv"), sep="" ) ; print(outFiles)
#!"G:\\DATA\\MacroData_D\\PCXVOL_D.csv" "G:\\DATA\\MacroData_D\\PCRX_D.csv"
#Download the file from the url into a temporary directory
tempFile <-"G:\\temp\\temp.csv"
inUrl = "http://www.cboe.com/publish/ScheduledTask/MktData/datahouse/indexPC.csv"
download.file(url=inUrl, destfile= tempFile, mode="w")
#Read the file
r <-read.csv(file=tempFile,skip=1, header=TRUE); print(head(r,3))
#! Trade_date Call Put Total P.C.Ratio
#!1 10/17/2003 134785 270102 404887 2.00
#!2 10/20/2003 110019 120439 230458 1.09
#!3 10/21/2003 125388 159054 284442 1.27
#!
#Extract the dates and the columns of interest
rdate <-r[,1]; print(head(rdate)) #! the first column is the Trade date
rvolume <-r[,4]; print(head(rvolume));
rPCR <-r[,5]; print(head(rPCR))
#format the data for output, example put the dates in ISO format
temp <- as.Date(rdate, format = "%m/%d/%Y"); print(head(temp))
rdate <-as.character(temp); print(head(rdate))
#Prepare five lines of comments and headers
headerVolume<- data.frame(Date=c(rep(" ",4),"Date"),indexVol=c(rep(" ",4),"indexVol" ))
headerVolume[1,] <-c("Source", inUrl)
headerVolume[2,1] <-"DateDownloaded"
headerVolume[2,2] <- as.character(as.Date(Sys.time()))
print(headerVolume)
headerPutCall <-data.frame(Date=c(rep(" ",4),"Date"),indexPC=c(rep(" ",4), "indexPC" ))
headerPutCall[1,] <-c("Source", inUrl)
headerPutCall[2,1] <-"DateDownloaded"
headerPutCall[2,2] <- as.character(as.Date(Sys.time()))
print(headerPutCall)
#Starting at row 6, write the file(s) to the output directory
#1. Prepare PC Volume series by combining date and data columns
v <-data.frame(Date=rdate, indexVol = rvolume); #! combine date and vol columns
print(head(v));
hv <-rbind(headerVolume, v); #! Stack 6 header lines on top of data
print(head(hv))
#2. Prepare Index PC Ratio series by combining date and data columns
idx <- data.frame(Date=rdate, indexPC = rPCR ); #! combine dates and PC ratio columns
print(head(v),10);
hidx <- rbind(headerPutCall, idx) #! Stack 6 header lines on top of data
print(head(hidx,10))
#Write the two files
write.table(x=hv, file=outFiles[1], sep=",", quote=FALSE, row.names=FALSE, col.names=FALSE)
write.table(x=hidx, file=outFiles[2], sep=",", quote=FALSE, row.names=FALSE, col.names=FALSE)
#read them back in and print top rows to check if correctly written
rvol <-read.csv(file= outFiles[1], header=FALSE) ;print(head(rvol,15))
ridx <-read.csv(file= outFiles[2], header=FALSE) ;print(head(ridx,15))
#Finally check if your WL datasource can read them :
#WL Datasource properties:
#!Version=3
#!Fields=Date,Close,
#!Extension=CSV
#!DateFormat=yyyy-mm-dd
#!TimeFormat=hh.nn
#!IgnoreLines=5
#!DecimalSep=Period
#!FieldSep=Comma
#!VolumeMult=1
#!ImpliedDecimals=0
#!Interval=1
#!Scale=0
#WL DATA (copied from Chart)
#! PCRX_D Open High Low Close Volume
#!2003-10-17 2 2 2 2 0
#!2003-10-20 1.0900000333786 1.0900000333786 1.0900000333786 1.0900000333786 0
#!2003-10-21 1.26999998092651 1.26999998092651 1.26999998092651 1.26999998092651 0
#!2003-10-22 1.88999998569489 1.88999998569489 1.88999998569489 1.88999998569489 0
#!2003-10-23 1.60000002384186 1.60000002384186 1.60000002384186 1.60000002384186 0
#!2003-10-24 1.49000000953674 1.49000000953674 1.49000000953674 1.49000000953674 0
#!2003-10-27 1.37000000476837 1.37000000476837 1.37000000476837 1.37000000476837 0
#!2003-10-28 1.05999994277954 1.05999994277954 1.05999994277954 1.05999994277954 0
#!
#!...
#!