Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Line chart to show stock movement

    I am new to this messaging, hope the sample arrived OK

    My problem: I track stocks weekly. Keep my totals weekly etc. All is good. However I really can't see how each individual stock is performing. (see sample) I want to see a line chart showing the movement of each stock as I enter price data. At present this data is not kept, therefore I know this is necessay. Is this something that a novice can manage? Or am I out of my realm?

    Larry
    Attached Files Attached Files
    Last edited by larrynz; 2011-02-03 at 10:59.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach an example workbook with the setup you want to use with more than 1 weeks worth of data. It can be fictitious data. Do you want to plot all the dates or just a subset for each stock?

    I would expect the addition of a "date column" (could be actual date, or use a week number) and then add an intermediate calc to extract the value of the stock of interest which woud be charted.

    Steve

  3. #3
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I only track once a week, so I expect there would be 52 entries during the year. Should these be placed in a row rather than a column?
    "then add an intermediate calc to extract the value of the stock of interest" I am not educated in code and not certain what this means. Actually I would like each chart to show when prices are added. Or so was my desire.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you provide a sample file after 2 months so we can see the structure? You only provide one week.

    I think the best way for the data would be in columns.

    Steve

  5. #5
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for answering Steve. The sample shows tracking data for 4 weeks and keeps track of total profolio. I do not Or have not saved individual stock prices on those four weeks. Any data could be set up as nothing is real in this tracker. Steve you say that columns would work best - that means there would be 52 down (for 52 weeks) for each stock. This creats a lot of numbers and would have to be on another sheet. Whereas when youger, I remember carrying info into another sheet, but have long forgotten. So if I place #s in the colums and each column represents an individual stock HOW: Do I transfer the data from sheet one to the price sheet? & How does it move forward one spot for the next weeks data? Sounds like it might be fun to start learning again.

    Larry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The sample shows tracking data for 4 weeks and keeps track of total profolio. I do not Or have not saved individual stock prices on those four weeks.
    Do you plan on saving each week or just this composite set? The setup looks like a summary and it seems to me that it would be the same with even 52 weeks of data. I thought you wanted to track the changes over time (week0by-week?) which means you would need the individual data...

    Any data could be set up as nothing is real in this tracker
    Yes it could. The dataset can be a representative sample of what you could have so that the setup can be examined and worked with.

    You can start with just an example chart of what you want to plot and/or what data you have/want. But you need to post an example with data in some form. The form may have to modified or the data extracted to make the chart, but I need some sample.

    Steve you say that columns would work best - that means there would be 52 down (for 52 weeks) for each stock. This creats a lot of numbers and would have to be on another sheet.
    I don't understand the comment. Why the need for multiple sets of data? You should have one master database where you will enter data. Some may need to be extracted (usually with formulas, pivot tables, sometimes with code) but you should not be keeping multiple datasets.

    HOW: Do I transfer the data from sheet one to the price sheet? & How does it move forward one spot for the next weeks data? Sounds like it might be fun to start learning again.
    The how would depend on what you have in the way of data and what you want to display (charts, summary data, etc). The data (as mentioned) can be fictitious, but I can't even create a random set since I am not sure what you will be collecting and what summaries and charts you want to keep.

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is this what you are after?

    I kept your summary sheet and added a price sheet with a date and the price for each stock that week. FOr each col it calcs the max and current (which the summary sheet gets) and the average (which I use in the chart). The data for the chart is extracted based on the combobox and is plotted on the chart along with the average.

    Steve
    Attached Files Attached Files

  8. #8
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Yes, that is close and thank you. Would it be possible to enter the price data into the chart when I enter it into the Sample Tracker? And where would the price data be stored?

    Larry

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Not sure what you are asking exactly.

    When you enter new data into the price sheet each week, the summary sheet will be updated with the current prices and the max prices, AND the chart will plot the new data...

    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    larrynz (2011-02-06)

  11. #10
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry Steve, I just didn't look well enough at the three tabs. You have shown me the code, so I should be able to transfer it to my spreadsheet. Question: Can I produce the chart on any page using the code that shows when I click on the chart. -- Maybe I should just play with it until I start understanding what you have done.

    I am very pleased with your work, not sure I would have ever figured it out.

    Larry
    (How could I get back to you if I have dificulities or screw things up?)

  12. #11
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Steve Do not understand -- =VLOOKUP(1E+300,B5:B56,1) -- I know B5:B56 refers to cell range with Vlookup as the function. But 1E+300 & the1 at the end? Help me please with this. Does it refer to the chart? Which brings me to the next question. Whereas I entered the prices in the main (only) sheet. I see you entered the prices in the PricesSheet, which transfered the price to the main page. (am I correct?). What or where did you start and what did you progress to next? Cause I am having problems, perhaps puting the cart before the horse..

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Question: Can I produce the chart on any page using the code that shows when I click on the chart.
    Yes the chart was created with the wizard using the date column and the intermediate value and average columns.

    (How could I get back to you if I have dificulities or screw things up?)
    Post back here with additional questions. I am a regular visitor...

    Do not understand -- =VLOOKUP(1E+300,B5:B56,1) -- I know B5:B56 refers to cell range with Vlookup as the function. But 1E+300 & the1 at the end? Help me please with this. Does it refer to the chart?
    No it has nothing to do with the chart. The 1E+300 is just a very large number. It is scientific notation (it means 1 x 10^300, which is the number 1 followed by 300 zeroes, it is near the limit of excels numeric capability). This is a "trick" to get the final number in a list. Vlookup keeps looking for the large number, and since it can not find a number larger (which is what Vlookup keys on) it just gets the last number in the list and stops. Therefore this formula gives the "current price": the last number entered into the list...

    Whereas I entered the prices in the main (only) sheet. I see you entered the prices in the PricesSheet, which transfered the price to the main page. (am I correct?).
    Not precisely. I have the prices entered in the pricesheet, but it is the summary sheet formula which extracts the data from the price sheet. If you want only 1 sheet you can move (not copy) those columns from the price sheet onto the summary sheet and everything should still work. If you move everything from the price sheet, the sheet can be deleted. The chart can even be added below the summary to put all onto one sheet.

    What or where did you start and what did you progress to next? Cause I am having problems, perhaps puting the cart before the horse..
    I started with your sheet. I then added the price sheet, added the headers based on the stocks you list and added dates (I presumed weekly was Monday, though any date could do). I then filled in some average prices.

    I then added some rows at the top to get me the current price and the max price for each column.

    I went to the summary sheet and replaced the max and current prices with a formula linking to the formulas in the price sheet so that the summary is always current with the newly entered prices.

    After gettng the summary sheet "live" I worked on the chart. I added a forms combobox to allow selection of the stock, and used the cell link with INDEX to extract the selected data from the appropriate column and put it into an intermediate column which I use to plot.

    As mentioned you could put the chart on the summary sheet. With only 3 stocks you could dispense with the selecting and just display all three charts, but I presumed that 3 was only an example set and built the more generic to select a chart rather than creating multiple charts.

    Hope this helps. If nothing else, put an example workbook of how you want the data set up and I can see what I can do...

    Steve

  14. #13
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Steve I am trying to send you my real Excel sheet. However I don't see anyway to upload it. Do I have to open another thread? Also I would like to keep this file private. Is this possible?

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are in the "QUICK REPLY" box, press the "Go Advanced" button.

    Underneath the "Your Message" you should now see a "Manage Attachments" button. Press that and you can browse to find the file and then upload it...

    Steve

  16. #15
    Lounger
    Join Date
    Mar 2010
    Location
    United States
    Posts
    35
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Real one

    Besides learning about Excel, it looks like I need to learn about this forum process. Easy once you find it.

    About the file: The info beneath the mail program is just for a simple look to see what has happened over the past year - It can be deleted. The yearly hi's & lo, I do wish to keep, but could go elsewhere. I update at the end of each week.
    Attached Files Attached Files

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •