Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked references'
in a few workbooks, without having to click the 'No' button in the
dialog box that displays when I open each workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab', (Ask to
update automatic links), then I don't get the 'ask" dialog box upon file
opening, but then the links update automatically.

I have three columns of stock quote data that I download, and I'd like
to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Thank you in advance for any help.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

You Got It. the best and only way is to remove your links.

"dk_" wrote:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked references'
in a few workbooks, without having to click the 'No' button in the
dialog box that displays when I open each workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab', (Ask to
update automatic links), then I don't get the 'ask" dialog box upon file
opening, but then the links update automatically.

I have three columns of stock quote data that I download, and I'd like
to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Thank you in advance for any help.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

I don't get what you mean. If I remove the links, then I cannot update
the stock quotes.

Why is no one helping here??? I would like to understand a good method
form updating data into a workbook from an external workbook? This must
be a common basice function.

Thanks.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture




In article ,
fluffymoore wrote:

You Got It. the best and only way is to remove your links.

"dk_" wrote:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked references'
in a few workbooks, without having to click the 'No' button in the
dialog box that displays when I open each workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab', (Ask to
update automatic links), then I don't get the 'ask" dialog box upon file
opening, but then the links update automatically.

I have three columns of stock quote data that I download, and I'd like
to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Thank you in advance for any help.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the 'No'
button in the dialog box that displays when I open each workbook
containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab', (Ask
to update automatic links), then I don't get the 'ask" dialog box
upon file opening, but then the links update automatically.

I have three columns of stock quote data that I download, and
I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download often
from my broker. I have the main sheet set up as series either of
references to the appropriate columns in the hidden sheet, or of
calculated fields of my own devise that build on the broker's data.
(E.g., percent gain, annualized percent gain, etc., etc.)
(I even have the header-row title in the calculated fields
formatted differently -- different color -- from the title
in the linked ("imported," more or less) columns/fields,
so I can know at a glance which is my data and which is
the broker's.

I have a macro that refreshes the data sheet from it source
and fixes up the rows on my main, visible sheet to match the
new data.

I also use some conditional formatting to cross-check for
data validation. Sometimes the broker makes a mistake!
E.g., if Cost was $5,000.00 and proceeds were $5,555.55 but
the Realized Gain is listed as $555.53, my cell turns pink.
If I bought 100 shares @ $50.00 but the Cost says $5123.45,
that cell turns orange. And so on.

Are these ideas at all helpful?

-dman-

Dallman,

See if I understand your setup...

Your hidden sheet is always updated when the workbook is opened, and
Excel doesn't 'ask' you 'yes or no' for the update permission. But then
it sounds like you have written a macro that will copy the data from
your hidden sheet when you 'run' the macro?

Do I understand correctly what described?

How does your macro work? ...Does your macro simply go to your hidden
sheet, select the range, then copy it, dese;ect the range, then return
to your main sheet and 'paste' the data?

-Dennis

In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the 'No'
button in the dialog box that displays when I open each workbook
containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab', (Ask
to update automatic links), then I don't get the 'ask" dialog box
upon file opening, but then the links update automatically.

I have three columns of stock quote data that I download, and
I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download often
from my broker. I have the main sheet set up as series either of
references to the appropriate columns in the hidden sheet, or of
calculated fields of my own devise that build on the broker's data.
(E.g., percent gain, annualized percent gain, etc., etc.)
(I even have the header-row title in the calculated fields
formatted differently -- different color -- from the title
in the linked ("imported," more or less) columns/fields,
so I can know at a glance which is my data and which is
the broker's.

I have a macro that refreshes the data sheet from it source
and fixes up the rows on my main, visible sheet to match the
new data.

I also use some conditional formatting to cross-check for
data validation. Sometimes the broker makes a mistake!
E.g., if Cost was $5,000.00 and proceeds were $5,555.55 but
the Realized Gain is listed as $555.53, my cell turns pink.
If I bought 100 shares @ $50.00 but the Cost says $5123.45,
that cell turns orange. And so on.

Are these ideas at all helpful?

-dman-

In ,
dk_ spake thusly:

Dallman,

See if I understand your setup...

Your hidden sheet is always updated when the workbook is
opened, and Excel doesn't 'ask' you 'yes or no' for the update
permission. But then it sounds like you have written a macro
that will copy the data from your hidden sheet when you 'run'
the macro?

Not quite, though close. I'll explain further:

1. I download the new data from my broker manually, usually at
least daily. (I am a very active trader. It's what I do for
a living.) I download it as a CSV (comma-separated values)
data-file.

2. I have a sheet set up in Excel that points to the CSV
file as a data source. I set this up initially using the menu
selection: Data - Import External Data - Import Data. Ever
after from the time I set that up, I can simply go to Data -
Refresh Data now on the main menu to pull in the data from the
current file I've just downloaded.

3. I want to do lots more than just display the data the broker
can show me. (Otherwise, I wouldn't need Excel to see it.
I could just look on the broker's web page at my transaction
details.) :-) The rest of my setup, I created to accommodate
my needs in that regard. A main consideration was data
integrity: I found, over the years of using a more direct
approach, that I could too easily introduce errors into my
spreadsheet and not know about it right away. I got very
fatigued with noticing at the oddest moments that something
seemed wrong with my data, then having to spend the next
three-quarter-hour finding the error (often to the tune of a
penny or two!). So that was the impetus for how I've designed
things now.

a) I don't want to disturb the broker's data; that's what
led to the introduction of errors in the past. So I set
up another sheet -- the main one -- into which I simply
reference the columnar data from the imported data sheet.
This is the sheet I can manipulate without fear of trashing
my data. Since I don't want to stare at the tabbed sheet
holding the imported broker's data, I hide it most of
the time. I also protect that sheet.

b) Note that I don't need every column the broker provides.
Suppose, of the 12 columns of supplied data, I'm interested
in working with 9 (and in a different displayed order from
that provided, as well); and I have another 21 columns of
my own devise that comprise calculated fields with more
sophisticated analyses of my trades. I also have some
hidden helper-columns on my main sheet.

c) All the messy steps for updating are done with a macro,
following the manual download of the CSV file. The hidden
sheet is unhidden and gets unprotected; the data query is
refreshed; named ranges are recalculated; the data sheet
is protected once more and again hidden. Now the macro
goes to the main sheet. It unhides all hidden columns
and unfilters all filtered data. It sorts based on a
normally-hidden key column that is simply a reference to
the row numbers on the data sheet. It adds or deletes rows
as needed and re-drags the formulas from the top row to
the new bottom. I have too much data for fast operation
if all the formulas are left in place, so the macro then
changes all cells from formulas to values, except for the
first row. Then it sorts again to my nominal display
preference. (Now the "first," formula, row -- the only
one left that really contains my formulas -- is somewhere
in the middle. I can get back from values to formulas
by re-sorting based on the key, which brings the first,
formula, row back to the top; then dragging down to fill
the formulas to the bottom.) Then the macro re-filters
data to my nominal preference and hides the helper columns
that are normally hidden.

i. There are actually two main sheets and two data sheets:
realized gains and unrealized (current portfolio).
The macro cycles through both doing similar tasks.
There are also chart sheets that update automatically
based on the most recent data.

I'm still cleaning up the last part of the macro. I've had help
here with it in the last month, most especially from Dave Peterson,
who was willing to baby me through the learning process. (Thanks,
Dave!)

Any more questions?

-dman-

================================================== ==================
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the
'No' button in the dialog box that displays when I open each
workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab',
(Ask to update automatic links), then I don't get the 'ask"
dialog box upon file opening, but then the links update
automatically.

I have three columns of stock quote data that I download,
and I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download
often from my broker. I have the main sheet set up as series
either of references to the appropriate columns in the hidden
sheet, or of calculated fields of my own devise that build on
the broker's data. (E.g., percent gain, annualized percent
gain, etc., etc.) (I even have the header-row title in the
calculated fields formatted differently -- different color
-- from the title in the linked ("imported," more or less)
columns/fields, so I can know at a glance which is my data and
which is the broker's.

I have a macro that refreshes the data sheet from it source and
fixes up the rows on my main, visible sheet to match the new
data.

I also use some conditional formatting to cross-check for data
validation. Sometimes the broker makes a mistake! E.g., if
Cost was $5,000.00 and proceeds were $5,555.55 but the Realized
Gain is listed as $555.53, my cell turns pink. If I bought 100
shares @ $50.00 but the Cost says $5123.45, that cell turns
orange. And so on.

Are these ideas at all helpful?

-dman-

Dallman 'dman',

I've read and saved your description below. Thank you for that. Sounds
like it was quite an interesting and fun project to develop.

I'm still stuck at the first part...

I'm running Excel97.

On the DATA Menu, I did not see what you described.

There is a 'GetExternal Data', then 3 sub menus:
1) Run Web Query...
2) Run Database Query...
3) Create New Query...

I gave each sub menu a try, but I could see no way to select a .csv
file.

So, I'm still stuck there.

....Any ideas? ...anyone???

Thanks.

-Dennis


------------------------------------------
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Dallman,

See if I understand your setup...

Your hidden sheet is always updated when the workbook is
opened, and Excel doesn't 'ask' you 'yes or no' for the update
permission. But then it sounds like you have written a macro
that will copy the data from your hidden sheet when you 'run'
the macro?

Not quite, though close. I'll explain further:

1. I download the new data from my broker manually, usually at
least daily. (I am a very active trader. It's what I do for
a living.) I download it as a CSV (comma-separated values)
data-file.

2. I have a sheet set up in Excel that points to the CSV
file as a data source. I set this up initially using the menu
selection: Data - Import External Data - Import Data. Ever
after from the time I set that up, I can simply go to Data -
Refresh Data now on the main menu to pull in the data from the
current file I've just downloaded.

3. I want to do lots more than just display the data the broker
can show me. (Otherwise, I wouldn't need Excel to see it.
I could just look on the broker's web page at my transaction
details.) :-) The rest of my setup, I created to accommodate
my needs in that regard. A main consideration was data
integrity: I found, over the years of using a more direct
approach, that I could too easily introduce errors into my
spreadsheet and not know about it right away. I got very
fatigued with noticing at the oddest moments that something
seemed wrong with my data, then having to spend the next
three-quarter-hour finding the error (often to the tune of a
penny or two!). So that was the impetus for how I've designed
things now.

a) I don't want to disturb the broker's data; that's what
led to the introduction of errors in the past. So I set
up another sheet -- the main one -- into which I simply
reference the columnar data from the imported data sheet.
This is the sheet I can manipulate without fear of trashing
my data. Since I don't want to stare at the tabbed sheet
holding the imported broker's data, I hide it most of
the time. I also protect that sheet.

b) Note that I don't need every column the broker provides.
Suppose, of the 12 columns of supplied data, I'm interested
in working with 9 (and in a different displayed order from
that provided, as well); and I have another 21 columns of
my own devise that comprise calculated fields with more
sophisticated analyses of my trades. I also have some
hidden helper-columns on my main sheet.

c) All the messy steps for updating are done with a macro,
following the manual download of the CSV file. The hidden
sheet is unhidden and gets unprotected; the data query is
refreshed; named ranges are recalculated; the data sheet
is protected once more and again hidden. Now the macro
goes to the main sheet. It unhides all hidden columns
and unfilters all filtered data. It sorts based on a
normally-hidden key column that is simply a reference to
the row numbers on the data sheet. It adds or deletes rows
as needed and re-drags the formulas from the top row to
the new bottom. I have too much data for fast operation
if all the formulas are left in place, so the macro then
changes all cells from formulas to values, except for the
first row. Then it sorts again to my nominal display
preference. (Now the "first," formula, row -- the only
one left that really contains my formulas -- is somewhere
in the middle. I can get back from values to formulas
by re-sorting based on the key, which brings the first,
formula, row back to the top; then dragging down to fill
the formulas to the bottom.) Then the macro re-filters
data to my nominal preference and hides the helper columns
that are normally hidden.

i. There are actually two main sheets and two data sheets:
realized gains and unrealized (current portfolio).
The macro cycles through both doing similar tasks.
There are also chart sheets that update automatically
based on the most recent data.

I'm still cleaning up the last part of the macro. I've had help
here with it in the last month, most especially from Dave Peterson,
who was willing to baby me through the learning process. (Thanks,
Dave!)

Any more questions?

-dman-

================================================== ==================
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the
'No' button in the dialog box that displays when I open each
workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab',
(Ask to update automatic links), then I don't get the 'ask"
dialog box upon file opening, but then the links update
automatically.

I have three columns of stock quote data that I download,
and I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download
often from my broker. I have the main sheet set up as series
either of references to the appropriate columns in the hidden
sheet, or of calculated fields of my own devise that build on
the broker's data. (E.g., percent gain, annualized percent
gain, etc., etc.) (I even have the header-row title in the
calculated fields formatted differently -- different color
-- from the title in the linked ("imported," more or less)
columns/fields, so I can know at a glance which is my data and
which is the broker's.

I have a macro that refreshes the data sheet from it source and
fixes up the rows on my main, visible sheet to match the new
data.

I also use some conditional formatting to cross-check for data
validation. Sometimes the broker makes a mistake! E.g., if
Cost was $5,000.00 and proceeds were $5,555.55 but the Realized
Gain is listed as $555.53, my cell turns pink. If I bought 100
shares @ $50.00 but the Cost says $5123.45, that cell turns
orange. And so on.

Are these ideas at all helpful?

-dman-

In ,
dk_ spake thusly:

Dallman 'dman',

I've read and saved your description below. Thank you for
that. Sounds like it was quite an interesting and fun project to
develop.

I'm still stuck at the first part...

I'm running Excel97.

On the DATA Menu, I did not see what you described.

There is a 'GetExternal Data', then 3 sub menus:
1) Run Web Query...
2) Run Database Query...
3) Create New Query...

I suspect you'd want (3). However, you don't need to do
it that way. What happens if you simply use Windows Explorer
to go to the CSV file, and then double-click on it? For
me and my Excel 2002, it opens the file by importing it into
Excel. That's just as good as the way I did it originally.
All we want is the data pulled into a sheet, which we now save
as a native Excel sheet.

Okay, I just did that as an experiment. It doesn't set up
a query structure in that case, so I can't just click the
"Refresh Data" option from the Data menu afterward. But I
could just re-import the new CSV the same way.

I suspect you can create a query in xl97, though, from
a CSV file as source, that behaves similarly to what I have.
I just went through all the steps again to see if I left
anything off in my description. I did: after I select
"Import Data" (which selection you don't have), I have
to select Text Files as the type in order to see CSVs
(or else I can just type "*.*" in the File name area
to see them as well). Then I navigate to my CSV file
and click Open, which initializes the Text Import Wizard.
I have to set it up to use comma-delimited as the option
for importing the data. When I'm asked where to puyt
the data, I accept the default "=$A$1" . After that,
thinks look just as they did via the method, two paragraphs
up, wherein I simply click on a CSV file in Explorer.

However, now the Refresh Data option is available from
the Data menu. I've set up a refreshable data query to a
text-based file, in other words.


Another point about how I do this: it isn't really
necessary to have both the broker's CSV file and a
hidden worksheet with the same thing on it. I could
just use the CSV file; I'd then open it (could be
via a macro), copy the data I want, and close it.
The original data would still be there in case I
fear I've corrupted my data in Excel again. But
the way i do it, I'm able to have my home-brewed
data validation going on via conditional formatting,
as I described earlier. And if I need to, I can
just unhide the hidden sheet (or even one of my hidden
columns on my main sheet) to compare things with the
"original."

One could alternatively simply import the desired
ranges into the current, main, sheet but hide those
columns; then reference the very same data in neighboring
columns that would also be manipulable with formulas.

Good luck, Dennis!

-dman-

------------------------------------------
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Dallman,

See if I understand your setup...

Your hidden sheet is always updated when the workbook is
opened, and Excel doesn't 'ask' you 'yes or no' for the update
permission. But then it sounds like you have written a macro
that will copy the data from your hidden sheet when you 'run'
the macro?

Not quite, though close. I'll explain further:

1. I download the new data from my broker manually, usually at
least daily. (I am a very active trader. It's what I do for
a living.) I download it as a CSV (comma-separated values)
data-file.

2. I have a sheet set up in Excel that points to the CSV
file as a data source. I set this up initially using the menu
selection: Data - Import External Data - Import Data. Ever
after from the time I set that up, I can simply go to Data -
Refresh Data now on the main menu to pull in the data from the
current file I've just downloaded.

3. I want to do lots more than just display the data the broker
can show me. (Otherwise, I wouldn't need Excel to see it.
I could just look on the broker's web page at my transaction
details.) :-) The rest of my setup, I created to accommodate
my needs in that regard. A main consideration was data
integrity: I found, over the years of using a more direct
approach, that I could too easily introduce errors into my
spreadsheet and not know about it right away. I got very
fatigued with noticing at the oddest moments that something
seemed wrong with my data, then having to spend the next
three-quarter-hour finding the error (often to the tune of a
penny or two!). So that was the impetus for how I've designed
things now.

a) I don't want to disturb the broker's data; that's what
led to the introduction of errors in the past. So I set
up another sheet -- the main one -- into which I simply
reference the columnar data from the imported data sheet.
This is the sheet I can manipulate without fear of trashing
my data. Since I don't want to stare at the tabbed sheet
holding the imported broker's data, I hide it most of
the time. I also protect that sheet.

b) Note that I don't need every column the broker provides.
Suppose, of the 12 columns of supplied data, I'm interested
in working with 9 (and in a different displayed order from
that provided, as well); and I have another 21 columns of
my own devise that comprise calculated fields with more
sophisticated analyses of my trades. I also have some
hidden helper-columns on my main sheet.

c) All the messy steps for updating are done with a macro,
following the manual download of the CSV file. The hidden
sheet is unhidden and gets unprotected; the data query is
refreshed; named ranges are recalculated; the data sheet
is protected once more and again hidden. Now the macro
goes to the main sheet. It unhides all hidden columns
and unfilters all filtered data. It sorts based on a
normally-hidden key column that is simply a reference to
the row numbers on the data sheet. It adds or deletes rows
as needed and re-drags the formulas from the top row to
the new bottom. I have too much data for fast operation
if all the formulas are left in place, so the macro then
changes all cells from formulas to values, except for the
first row. Then it sorts again to my nominal display
preference. (Now the "first," formula, row -- the only
one left that really contains my formulas -- is somewhere
in the middle. I can get back from values to formulas
by re-sorting based on the key, which brings the first,
formula, row back to the top; then dragging down to fill
the formulas to the bottom.) Then the macro re-filters
data to my nominal preference and hides the helper columns
that are normally hidden.

i. There are actually two main sheets and two data sheets:
realized gains and unrealized (current portfolio).
The macro cycles through both doing similar tasks.
There are also chart sheets that update automatically
based on the most recent data.

I'm still cleaning up the last part of the macro. I've had help
here with it in the last month, most especially from Dave Peterson,
who was willing to baby me through the learning process. (Thanks,
Dave!)

Any more questions?

-dman-

================================================== ==================
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the
'No' button in the dialog box that displays when I open each
workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab',
(Ask to update automatic links), then I don't get the 'ask"
dialog box upon file opening, but then the links update
automatically.

I have three columns of stock quote data that I download,
and I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download
often from my broker. I have the main sheet set up as series
either of references to the appropriate columns in the hidden
sheet, or of calculated fields of my own devise that build on
the broker's data. (E.g., percent gain, annualized percent
gain, etc., etc.) (I even have the header-row title in the
calculated fields formatted differently -- different color
-- from the title in the linked ("imported," more or less)
columns/fields, so I can know at a glance which is my data and
which is the broker's.

I have a macro that refreshes the data sheet from it source and
fixes up the rows on my main, visible sheet to match the new
data.

I also use some conditional formatting to cross-check for data
validation. Sometimes the broker makes a mistake! E.g., if
Cost was $5,000.00 and proceeds were $5,555.55 but the Realized
Gain is listed as $555.53, my cell turns pink. If I bought 100
shares @ $50.00 but the Cost says $5123.45, that cell turns
orange. And so on.

Are these ideas at all helpful?

-dman-

Dallman, you are indeed dman!

Thank you, thank you, and thank you!

You did get me up and running with your 'query' instructions. Also, the
description of your setup got me started with several new ideas for my
workbooks.

In order to be able to 'query' a .csv file in Excel97, I had to load the
ODBC Add-in in Excel97. That gave me the option to Create a New Query,
with the New Data Source as '.csv' and '.xls.' ...This feature is just
most excellent!

I also like your idea of using a seperate sheet to bring in the raw data
from the .csv file.

As I write this reply, I ran into a problem with my querry. I moved the
folder that has the source and destination files of the query, and now
it's broken. I am totally lost how to correct this, or how to edit the
path or the query. :(

I use this same setup on Macintosh Excel98, in addition to the Windows
Excel97 version, but I can't figure out how to load or find a driver for
'.csv' or for '.xls'.


Q) How do you pull the data from your hidden sheet into your main
worksheet page? ...Do you just have a formula in each cell with
something like '="hiddensheetName"!A1", which would update your main
sheet everytime you run the query for your hidden sheet. Or, do you have
'manual control' of that part of your updating process also?

Dellman, thank you again for everything that you've already posted. Very
much appreciated. ...I'm having fun now. :)

-Dennis




In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Dallman 'dman',

I've read and saved your description below. Thank you for
that. Sounds like it was quite an interesting and fun project to
develop.

I'm still stuck at the first part...

I'm running Excel97.

On the DATA Menu, I did not see what you described.

There is a 'GetExternal Data', then 3 sub menus:
1) Run Web Query...
2) Run Database Query...
3) Create New Query...

I suspect you'd want (3). However, you don't need to do
it that way. What happens if you simply use Windows Explorer
to go to the CSV file, and then double-click on it? For
me and my Excel 2002, it opens the file by importing it into
Excel. That's just as good as the way I did it originally.
All we want is the data pulled into a sheet, which we now save
as a native Excel sheet.

Okay, I just did that as an experiment. It doesn't set up
a query structure in that case, so I can't just click the
"Refresh Data" option from the Data menu afterward. But I
could just re-import the new CSV the same way.

I suspect you can create a query in xl97, though, from
a CSV file as source, that behaves similarly to what I have.
I just went through all the steps again to see if I left
anything off in my description. I did: after I select
"Import Data" (which selection you don't have), I have
to select Text Files as the type in order to see CSVs
(or else I can just type "*.*" in the File name area
to see them as well). Then I navigate to my CSV file
and click Open, which initializes the Text Import Wizard.
I have to set it up to use comma-delimited as the option
for importing the data. When I'm asked where to puyt
the data, I accept the default "=$A$1" . After that,
thinks look just as they did via the method, two paragraphs
up, wherein I simply click on a CSV file in Explorer.

However, now the Refresh Data option is available from
the Data menu. I've set up a refreshable data query to a
text-based file, in other words.


Another point about how I do this: it isn't really
necessary to have both the broker's CSV file and a
hidden worksheet with the same thing on it. I could
just use the CSV file; I'd then open it (could be
via a macro), copy the data I want, and close it.
The original data would still be there in case I
fear I've corrupted my data in Excel again. But
the way i do it, I'm able to have my home-brewed
data validation going on via conditional formatting,
as I described earlier. And if I need to, I can
just unhide the hidden sheet (or even one of my hidden
columns on my main sheet) to compare things with the
"original."

One could alternatively simply import the desired
ranges into the current, main, sheet but hide those
columns; then reference the very same data in neighboring
columns that would also be manipulable with formulas.

Good luck, Dennis!

-dman-

------------------------------------------
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Dallman,

See if I understand your setup...

Your hidden sheet is always updated when the workbook is
opened, and Excel doesn't 'ask' you 'yes or no' for the update
permission. But then it sounds like you have written a macro
that will copy the data from your hidden sheet when you 'run'
the macro?

Not quite, though close. I'll explain further:

1. I download the new data from my broker manually, usually at
least daily. (I am a very active trader. It's what I do for
a living.) I download it as a CSV (comma-separated values)
data-file.

2. I have a sheet set up in Excel that points to the CSV
file as a data source. I set this up initially using the menu
selection: Data - Import External Data - Import Data. Ever
after from the time I set that up, I can simply go to Data -
Refresh Data now on the main menu to pull in the data from the
current file I've just downloaded.

3. I want to do lots more than just display the data the broker
can show me. (Otherwise, I wouldn't need Excel to see it.
I could just look on the broker's web page at my transaction
details.) :-) The rest of my setup, I created to accommodate
my needs in that regard. A main consideration was data
integrity: I found, over the years of using a more direct
approach, that I could too easily introduce errors into my
spreadsheet and not know about it right away. I got very
fatigued with noticing at the oddest moments that something
seemed wrong with my data, then having to spend the next
three-quarter-hour finding the error (often to the tune of a
penny or two!). So that was the impetus for how I've designed
things now.

a) I don't want to disturb the broker's data; that's what
led to the introduction of errors in the past. So I set
up another sheet -- the main one -- into which I simply
reference the columnar data from the imported data sheet.
This is the sheet I can manipulate without fear of trashing
my data. Since I don't want to stare at the tabbed sheet
holding the imported broker's data, I hide it most of
the time. I also protect that sheet.

b) Note that I don't need every column the broker provides.
Suppose, of the 12 columns of supplied data, I'm interested
in working with 9 (and in a different displayed order from
that provided, as well); and I have another 21 columns of
my own devise that comprise calculated fields with more
sophisticated analyses of my trades. I also have some
hidden helper-columns on my main sheet.

c) All the messy steps for updating are done with a macro,
following the manual download of the CSV file. The hidden
sheet is unhidden and gets unprotected; the data query is
refreshed; named ranges are recalculated; the data sheet
is protected once more and again hidden. Now the macro
goes to the main sheet. It unhides all hidden columns
and unfilters all filtered data. It sorts based on a
normally-hidden key column that is simply a reference to
the row numbers on the data sheet. It adds or deletes rows
as needed and re-drags the formulas from the top row to
the new bottom. I have too much data for fast operation
if all the formulas are left in place, so the macro then
changes all cells from formulas to values, except for the
first row. Then it sorts again to my nominal display
preference. (Now the "first," formula, row -- the only
one left that really contains my formulas -- is somewhere
in the middle. I can get back from values to formulas
by re-sorting based on the key, which brings the first,
formula, row back to the top; then dragging down to fill
the formulas to the bottom.) Then the macro re-filters
data to my nominal preference and hides the helper columns
that are normally hidden.

i. There are actually two main sheets and two data sheets:
realized gains and unrealized (current portfolio).
The macro cycles through both doing similar tasks.
There are also chart sheets that update automatically
based on the most recent data.

I'm still cleaning up the last part of the macro. I've had help
here with it in the last month, most especially from Dave Peterson,
who was willing to baby me through the learning process. (Thanks,
Dave!)

Any more questions?

-dman-

================================================== ==================
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the
'No' button in the dialog box that displays when I open each
workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab',
(Ask to update automatic links), then I don't get the 'ask"
dialog box upon file opening, but then the links update
automatically.

I have three columns of stock quote data that I download,
and I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download
often from my broker. I have the main sheet set up as series
either of references to the appropriate columns in the hidden
sheet, or of calculated fields of my own devise that build on
the broker's data. (E.g., percent gain, annualized percent
gain, etc., etc.) (I even have the header-row title in the
calculated fields formatted differently -- different color
-- from the title in the linked ("imported," more or less)
columns/fields, so I can know at a glance which is my data and
which is the broker's.

I have a macro that refreshes the data sheet from it source and
fixes up the rows on my main, visible sheet to match the new
data.

I also use some conditional formatting to cross-check for data
validation. Sometimes the broker makes a mistake! E.g., if
Cost was $5,000.00 and proceeds were $5,555.55 but the Realized
Gain is listed as $555.53, my cell turns pink. If I bought 100
shares @ $50.00 but the Cost says $5123.45, that cell turns
orange. And so on.

Are these ideas at all helpful?

-dman-

In ,
dk_ spake thusly:

Dallman, you are indeed dman!

Thank you, thank you, and thank you!

Very glad it was so helpful.

You did get me up and running with your 'query'
instructions. Also, the description of your setup got me started
with several new ideas for my workbooks.

Great!

In order to be able to 'query' a .csv file in Excel97, I had
to load the ODBC Add-in in Excel97. That gave me the option
to Create a New Query, with the New Data Source as '.csv' and
'.xls.' ...This feature is just most excellent!

Ah. That's good to know about.

I also like your idea of using a seperate sheet to bring in the
raw data from the .csv file.

In another workbook of mine, I do it by importing into my main
sheet, but I keep most of the imported columns hidden. Then I
have created new columns that reference those hidden columns,
but perform calculated operations on the data at the same time.
But the system I described, that you like, is my newest approach.
I do find it has various advantages in straightforwardness and
robustness.


As I write this reply, I ran into a problem with my querry. I
moved the folder that has the source and destination files of
the query, and now it's broken. I am totally lost how to correct
this, or how to edit the path or the query. :(

I'm not the best person to ask, because I don't know more than
some basics about queries at this stage. But while I'm sure
one ought to be able to find a menu to edit a query, I also
think it might be easiest just to create the query anew with
the new location.

I use this same setup on Macintosh Excel98, in addition to the
Windows Excel97 version, but I can't figure out how to load or
find a driver for '.csv' or for '.xls'.

Maybe someone can help, because I wouldn't know that either.
However, CSV files certainly can be read with the text-import
wizard. You would specify delimited fields and tell it the comma
is the delimiter.

Q) How do you pull the data from your hidden sheet into your
main worksheet page? ...Do you just have a formula in each cell
with something like '="hiddensheetName"!A1", which would update
your main

Exactly.

sheet everytime you run the query for your hidden sheet. Or, do
you have 'manual control' of that part of your updating process
also?

I thought about doing it that way, and it ought to work fine also.
But I'm happy with my approach for the time being. An advantage
for my heuristic with my setup is the "primary key" column I created
(as Column A) in my main sheet. It allows for the easiest sorting
to revert my order back to that of the broker's default order for
troubleshooting, etc. Here's what's in (unsorted) A2, just below
my header:

=ROW('2006 Realized - CSV Data'!A2)

and that's my "primary key."


Dellman, thank you again for everything that you've already
posted. Very much appreciated. ...I'm having fun now. :)

Glad you are getting good ideas out of it, Dennis.

-dman-