Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Complex Macrotable select & Apply Average function (Excel 2003)

    I have a spreadsheet that automatically downloads from yahoo monthly historical quotes for a given list of symbols and then gives me the average price for that symbol for those 2 dates. I find that the loop drops off the end date which is the first line in the table of the datasheet for each stock quote download when it calculates the average.
    The spreadsheet attached works as follows:
    symbol list sheet allows to to set to and from dates and gives the final average result when all the macros are run by pushing update button in the worksheet sheet
    worksheet sheet gives the button to set all in motion
    The data sheet gives the raw data download that the macros work off of
    What would be the correct changes to the macroaverage loop to include the end date.? I tired to attach the file but even zipped it is 133 not 100
    So Here are table and the macro codeaverage
    Jerome

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    I'm sorry, I don't think I can answer your question seeing only the workbook you attached. Perhaps you can create a stripped down version of the data workbook - we don't need to see ALL data, just enough to get an idea what you're working with.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Hans:
    Thanks there is no real data in the stripped down version, seems the code takes a lot of kb.
    Any way I can email it to you it is about 1mb or 135 kb in zip form.??
    Jerome

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Do we need to see the code? The problem I had with the workbook you posted higher up in this thread is that I don't know what the data processed by the macro look like.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Hans:
    Good morning
    The data is downloaded automatically from yahoo as historical stock data. The code has about 5 macros and I sent you the macro code below the table for averaging.
    I know itis hard to break apart without breaking the whole thing.
    Easier for you to understand if you run the spread sheet and see how it works and what it dumps.
    Alternatively I can post it on my web site and send you the address, I don't want the whole world to see who i am in detail associated with stocks
    Alternatively the web dump per stock yields an appended table of the following results assuming you run the following statements
    http://table.finance.yahoo.com/d?a=0&b...mp;g=m&s=AV
    http://table.finance.yahoo.com/d?a=0&b...p;g=m&s=BLS
    Also people may enjoy seeing it as it has a lot of tax usefullness as it yields the monthly average which is requested vs the closing dates by tax people. You may be able to compress it more for posting
    Regards
    Jerome

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    You can send me the URL in a PM, if you like.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Hans
    All done
    Regards and thanks
    Jerome

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    If you verify the result by checking the average of the results dumped on the table vs the average next to the stop that is calculated. The only way the calculated average matches is if you zero out the first row ie the last date of the table dump the most recent result and then the the numbers match. So the loop for some reason is excluding the "to" date and not including the final month when it calculates the average.
    Jerome

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    I'm sorry, I haven't received a PM yet.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Hans I double clcked on your name and went to the mail icon and sent it to you that way.
    Did I do it incorrectly

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    I tried it again
    Jerome

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Hans Greetings
    Did you get the url???
    Regards
    Jerome

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Yes, I was away from my PC, but I did get the PM. I will look at it later.

  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    Thanks, Just wanted to make certain I did not louse it up again
    Regards
    Jerome

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Macrotable select & Apply Average function (Excel 2003)

    In the CalculateAverage procedure, the start value for the variable j is 3, so Range("G" & j & ":G" & j + 17) is G3:G20. The data in column G start in G2, however, so shouldn't the initial value be j = 2?

Page 1 of 2 12 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
  •