Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel: Format Control: 'cell link' reamins constant. How to copy | Excel Worksheet Functions | |||
Updating Page Field of Pivot Tables from Form Control | Excel Discussion (Misc queries) | |||
how to set up a link from a control box? | Excel Worksheet Functions | |||
How do I link a macro to an ActiveX control? | New Users to Excel | |||
How do I link a macro to an ActiveX control? | New Users to Excel |