Exporting FDC output to an ASCII file or Excel® spreadsheet
Although most FDC users function entirely within FDC, some have requested the ability to export into other formats. The main reason to do this is to share information with someone who does not have FDC. If you were sharing information with another FDC user, just send them an email and attach a copy of the appropriate FDC file.
The first thing to ascertain is whether this exporting will be a rare occurrence, or a regular one. We will first explain how to do a casual export, and then how you can set up a macro and command set loop to generate an everyday Excel export.
The Casual or One-Off Export:
Produce your FDC dataset and then go to:
This will result in FDC inserting another column with dates.
Then just highlight the part you wish to copy (or choose EDIT, SELECT ALL), COPY and PASTE into either Notepad® or Excel®.
As an alternative, you may also create a dataset and save it in ASCII format:
When saving as an ASCII file, it is not necessary to PACKAGE IN PLACE, as the file will be saved with its dates.
The Everyday Export (i.e. to an Excel Spreadsheet):
Let’s suppose that you need to create a table on a daily basis. Naturally you want to save time by doing it automatically. In the past you had created this table directly in Excel, but now you want to see if FDC can do it for you.
First, create a macro in FDC which will generate the actual output you need. We list the macro definition here. Should you wish to see a detailed explanation of what the macro actually does, we have listed it at the bottom of this document.
@ etf_rank
a: cl #R
b: 100* ( 2 movslope a ) last 1
c: 100* ( 4 movslope a) last 1
d: 100* ( 8 movslope a) last 1
e: 100* ( 16 movslope a) last 1
f: 100* ( 32 movslope a) last 1
g: 100* ( 64 movslope a) last 1
h: b, c, d, e, f, g
j: (round 100 *h)/100
k: j common (hsum j)
(a last 1) common k
The purpose of this macro is to calculate moving slopes of a particular asset over various time frames and create a new value based on the sum of the slopes of those different time frames. The result of this macro is a dataset consisting of one row only – the last day. The macro can be used on one asset or on a list of assets. For example, should you enter
, you will get the following output:
However our goal here is to have the macro operate on a list of assets, producing the same values for each such that the assets can be compared.
For the purposes of example, let’s compare the set of ETFs representing non-US economies. We already have a list of those ETFs.
So that our output data (consisting of one date only) does not overwrite the data in the existing datasets, we need to have another list just for output purposes. That is, we want the names to be similar, but we need some way to distinguish them from the names in our list “ETFs_Foreign_list”. So let’s add a suffix to the name of each item in the list. You can do this manually, but if you are doing this for 500 stocks, manually gets a bit tiresome. Here’s an easier way:
In the FDC List Wizard, open the list “ETFs_Foreign_list”.
Then: 
Copy the items in the list, and past them into a Notepad® (or Word®) file.
Then go to EDIT, and REPLACE, as illustrated below:
Then replace the “.fdc” suffixes with “_pm”. FDC knows that its data files are .fdc, so that suffix is redundant. I’ve used “_pm” to mean “post-merge”, but you can use any suffix as long as you modify the command set language accordingly. See below:
You want to 
.
Next, go back to the LIST WIZARD and 
.
Copy the new names 
into the edit box, and 
.
Give your list the name “ETFs_Foreign_pm_list” Note that this list is just a list to hold the output names of the computations to refer to later.
Next, create a command set which is illustrated below:
The lines in this command set are also explained at the bottom of this document.
Save this command set, as this is the operation you will be performing every day. Pick a name that means something to you, like “daily_etf_foreign_run”.
When you have run this command set, go to your FDC general folder. In it you will find a folder named “excel”:
Open it and double-click on 
, and you will see the output you requested:
The entire preparation may seem like a lot of work, but once you have done it, all you have to do in the future is
(a) open the command set,
(b) click on autoplay, and
(c) retrieve your Excel file.
---------------------------------------------------------------------------------------------------
@ etf_rank
a: cl #R
b: 100* ( 2 movslope a ) last 1
c: 100* ( 4 movslope a) last 1
d: 100* ( 8 movslope a) last 1
e: 100* ( 16 movslope a) last 1
f: 100* ( 32 movslope a) last 1
g: 100* ( 64 movslope a) last 1
h: b, c, d, e, f, g
j: (round 100 *h)/100
k: j common (hsum j)
(a last 1) common k
Local variable “a” is defined as the close of whatever dataset appears as #R.
Local variable “b” is defined as the 2-day moving slope of a. The moving slope is determined by running a least-squares regression through the data.
Local variables “c” through “g” do the same thing as “b” but for extended periods.
Local variable “h” lists all these moving slopes.
Local variable “j” multiplies them by 100, rounds them to integers and then divides those values by 100. The effect is to round the values to 2 decimal places. Local variable “k” lists all of these values, along with their sum.
Lastly, the output line produces the last closing price along with the various moving slopes and their sum.
---------------------------------------------------------------------------------------------------
The command set illustrated above functions as follows:
reset This line merely resets any temporary files.
:for #1 :in ETFs_Foreign_list This line selects the list “ETFs_Foreign_list ”, and indicates that the operation is to be performed on each item in that list.
('#1_pm') gets 'Price,2Day,4Day,8Day,16Day,32Day,64Day,RATING' setlabels etf_rank #1
The best way to read this line is from the right. The macro “etf_rank” is performed on each item in the list. Then a label is assigned. The labels are collectively in single quotes, comma delimited, and with no spaces. That having been done, each item in the list is then renamed with the suffix “_pm”. **
:endfor Ends the loop.
'etf_foreign_spreadsheet' mergetoexcel 'ETFs_Foreign_pm_list' takes each item in the new list (the one with suffixes) and puts them in an Excel spreadsheet, and saves the spreadsheet in the “excel” subfolder within the FDC general folder under the name “etf_foreign_spreadsheet”. Put differently, the function mergetoexcel has two arguments. The right argument “ETFs_Foreign_pm_list” contains the names of the datasets that are going to be merged. The left argument “etf_foreign_spreadsheet” is the name you have chosen for the eventual merged Excel file.
:stop Ends the autoplay operation.
** This line will save the “_pm” datasets into your main data folder. You may choose (as we do) to put them into a subfolder to keep the main folder uncluttered. In that case, all you need do is create the subfolder and specify the path to that subfolder. Here’s the instruction that we use, but be mindful that your path might be different:
('C:\Program Files\MathInvest\fdc\data\ETFs_Foreign\#1_pm') gets 'Price,2Day,4Day,8Day,16Day,32Day,64Day,RANK' setlabels etf_rank #1
---------------------------------------------------------------------------------------------------
etf_rank qqqq
reset
:for #1 :in ETFs_Foreign_list
('#1_pm') gets 'Price,2Day,4Day,8Day,16Day,32Day,64Day,RATING' setlabels etf_rank #1
:endfor
'etf_foreign_spreadsheet' mergetoexcel 'ETFs_Foreign_pm_list'
:stop
reset
:for #1 :in ETFs_Foreign_list
('C:\Program Files\MathInvest\fdc\data\ETFs_Foreign\#1_pm') gets 'Price,2Day,4Day,8Day,16Day,32Day,64Day,RATING' setlabels etf_rank #1
:endfor
'etf_foreign_spreadsheet' mergetoexcel 'ETFs_Foreign_pm_list'
:stop