Complex question about importing, analyzing data

[[email protected]]

I get a cvs file emailed to me. I am trying hard to figure out how to
import the data into Excel not in whole, but only selected columns.
Once that is done, I would like to run a formula such as subract d from
1 row from e from another row if and only if A is the same. Could
someone point me to a book or get me started and I will try to wrestle
with the rest?

[wjohnson]

What I would do is import the whole file into a Blank Worksheet - then delete what you don't want and then copy and past what you do want into your "Master Sheet"

As for the formula something like the following should work
=IF(D3=E3,D3+F3,"ignore")
Reads as follows If D3=E3, then if TRUE D3+F3, If false then "ignore" or whatever you want to put into the cell.

[[email protected]]

Thank you. I will try. I am not ready to give up yet. I have a
template that uses a find feature that I would like to do differently.
They use theoretical values and I want to use actual. I guess I could
explain. Value Line has a template that is available to download by
their subscribers. The worksheet is an option spread finder. In their
"spread finder" , a possible good spread is one that has a good
difference between theoretical and actual value. Its limitation that I
see is that you have to decide when searching what two of the criteria
are. (criterea or criterian?) Bull is always buying low and selling
high. Bear is buying high and selling low. First step is to load the
files. This pulls all the possible options in. then you chose if you
want to do a bull search, a bear seach, long or short. Then choose,
depending on the first choice, how many strike prices or how many
expirations. when the first step, the load is done, it pulls the data
onto the second sheet in the workbook. The search uses the criteria
mentioned. It uses the difference in theoreitical value to find the
spread of choice. My desire is to compare all options of the same
stock, and compare it against all available options with the same
expiration date. My if is to display all spreads, taking the
difference in strike price, whatever that may be, sometimes $2.5,
sometimes $5, and compare the asking price for the one that I must buy,
(you alsways buy at the asked price) and subtract from that, the bid
price, (you always sell at the bid). I want to be able to say, find me
all the spreads where the difference in strike prices - the cost,
divide this buy the cost. show me when either the result is less then
or equal to, or greater than or equal to a certain value of my
choosing. Will explain the why if I ahve to. Theirs has the imported
data, as I mentioned above, on the second page, and on the first the
search functions and results come up. The data on the second page is
one line per each option, the results are actually on one line with the
details of first the purchased option, the next few is the sold option,
and the results are after that. the prmium cost, the theoretical cost,
and the difference. Because on theirs, you have to specify how many
strike prices, say 1 or 2 or 3, etc, this may either be 2.5 or $5,
depending on the ones offered. What I am trying to do is find out the
good positions, irrespective of the difference in price, only that X
and Y fit the criteria that I put in, say 16% under, ( a good buy) or
16% over, ( a good short sale). this is getting wordy, forgive me.
for instance, american airlines, symbol AMR, ( jsut to name one, not
real values, find all that are < or = to -.15, or > or = to .15. the
search looks at the stick symbol for two options, lets say column a2,
and it is the same, so then it compares the cost of the 12.50, which is
5$ asked, and then looks at all of the others with the same a2 column,
so that it is comparing the same base apples to apples, and finds that
the bid of the $10 is $7.10, diff in strike is 2.5, diff in premium is
$2.1. cost of premiums is $2.1 from difference of strikes of 2.5, or
..4, that divided by $2.1 is .16, a hit if I used > .15. This is
getting wordy as I said.
I will try the formula and try to see if I can tweak what they have, or
could I ask for a good how to book or books, or a hint to my solution.
Again the formula is not hard, getting it to find and then to display
the criteria is my struggle.

[Peo Sjoblom]

If you rename the file extension from CSV to txt then you can open it and
the text import wizard will start, there in step 3 you can mark certain
column(s) and select do not import column (skip), then run your formula
If you don't want to change the file extension do data>import external
data>import data (that is for 2003, the wording might differ slightly in
earlier versions but it's basically the same), in the file name box put *.*
for all file types and if you open the CSV file from there it will also
trigger the text import wizard

--
Regards,

Peo Sjoblom

(No private emails please)


<[email protected]> wrote in message
news:[email protected]...
> Thank you. I will try. I am not ready to give up yet. I have a
> template that uses a find feature that I would like to do differently.
> They use theoretical values and I want to use actual. I guess I could
> explain. Value Line has a template that is available to download by
> their subscribers. The worksheet is an option spread finder. In their
> "spread finder" , a possible good spread is one that has a good
> difference between theoretical and actual value. Its limitation that I
> see is that you have to decide when searching what two of the criteria
> are. (criterea or criterian?) Bull is always buying low and selling
> high. Bear is buying high and selling low. First step is to load the
> files. This pulls all the possible options in. then you chose if you
> want to do a bull search, a bear seach, long or short. Then choose,
> depending on the first choice, how many strike prices or how many
> expirations. when the first step, the load is done, it pulls the data
> onto the second sheet in the workbook. The search uses the criteria
> mentioned. It uses the difference in theoreitical value to find the
> spread of choice. My desire is to compare all options of the same
> stock, and compare it against all available options with the same
> expiration date. My if is to display all spreads, taking the
> difference in strike price, whatever that may be, sometimes $2.5,
> sometimes $5, and compare the asking price for the one that I must buy,
> (you alsways buy at the asked price) and subtract from that, the bid
> price, (you always sell at the bid). I want to be able to say, find me
> all the spreads where the difference in strike prices - the cost,
> divide this buy the cost. show me when either the result is less then
> or equal to, or greater than or equal to a certain value of my
> choosing. Will explain the why if I ahve to. Theirs has the imported
> data, as I mentioned above, on the second page, and on the first the
> search functions and results come up. The data on the second page is
> one line per each option, the results are actually on one line with the
> details of first the purchased option, the next few is the sold option,
> and the results are after that. the prmium cost, the theoretical cost,
> and the difference. Because on theirs, you have to specify how many
> strike prices, say 1 or 2 or 3, etc, this may either be 2.5 or $5,
> depending on the ones offered. What I am trying to do is find out the
> good positions, irrespective of the difference in price, only that X
> and Y fit the criteria that I put in, say 16% under, ( a good buy) or
> 16% over, ( a good short sale). this is getting wordy, forgive me.
> for instance, american airlines, symbol AMR, ( jsut to name one, not
> real values, find all that are < or = to -.15, or > or = to .15. the
> search looks at the stick symbol for two options, lets say column a2,
> and it is the same, so then it compares the cost of the 12.50, which is
> 5$ asked, and then looks at all of the others with the same a2 column,
> so that it is comparing the same base apples to apples, and finds that
> the bid of the $10 is $7.10, diff in strike is 2.5, diff in premium is
> $2.1. cost of premiums is $2.1 from difference of strikes of 2.5, or
> .4, that divided by $2.1 is .16, a hit if I used > .15. This is
> getting wordy as I said.
> I will try the formula and try to see if I can tweak what they have, or
> could I ask for a good how to book or books, or a hint to my solution.
> Again the formula is not hard, getting it to find and then to display
> the criteria is my struggle.
>