Posts by sd11089

    Re: Excel "Not Responding" but still running code


    Lol sorry, I am using a lot of arrays. I'm not sure if it makes a difference, but I'm not sizing the arrays perfectly, I am just putting a number in that should be large enough when I Redim. The part posted below is calculating the correlation between the returns of a portfolio and a specified index. Here's the code for the slowest part of the program:


    Thank you both for the advice.
    -Stephen

    I am writing a program to generate all combinations of assets, with a specific portfolio size, from a group of tickers, and to also calculate a stats. If i run the program and it has more than just a few or so possible combinations, then excel will say (not responding) when it is calculating all the statistics, even though the code is running (If you sit and wait it eventually finishes, and the program is flashing the entire time). This is even happening when I just run the portfolio generation part with a large amount of tickers (it always gets caught around 10000 portfolios, but will still finish running). This is annoying because I want to be able to see the amount of portfolios it has gone through so I can know for sure it is working, and also because I cant stop the code from running without freezing excel.


    Just a heads up, I have only been writing code for about 6 months, and everything is self-taught, I tried to clean up the code, but I guarantee it can be more efficiently written and is probably still a little messy. I just want advice on how I can prevent excel from doing this, and also possibly advice on how to speed up the program (Specifically the part where I am calculating the Statistics and Especially the Portfolio Correlation part.)


    My File size is 789kb so I cant upload it, but if anyone can help with this let me know and I will email it to you.


    I am Running Excel 2010 64-Bit Edition on Windows 7

    I am attempting to create an automated point and figure chart but am stuck on how to actually make the "Chart." I want to have excel go down Column F and identify if the text is an "O" or an "X". If it is an "O" I want it to copy that "O" into Cell "J30". If the next cell is an "O" I want it copied into Cell "J31" and so on. If the value changes to an "X" I want it to copy that value into the cell next to the last "O". (i.e. if the last "O" value is in cell J35, I want the "X" column to start in K35). If the next value is an "X" I want it to copy in the cell above the previous "X". Whenever the value changes from X to O, I want it to start in the next column, next to the previously entered value. It should look something like:


    XO
    XO
    XOX
    OXOXO
    OXOXO
    OX OX
    OX OX
    OX OX


    I have little knowledge of VBE but am able to somewhat understand it an use it, but could you please reference the attached file for cell numbers.

    Re: Using Multiple Web Queries for hyperlinks in cells


    I really appreciate the help, but Im still stuck. I added worksheet 'Query' and ran this macro, but Excel says not responding every time. Im looking through this code, and am wondering if this is based off the excel sheet I posted earlier, because there is a reference to 'Sheet1' which I don't have. Also, am I supposed to move the ticker symbols I want to be processed onto the new Query page?

    I am wanting to do web queries for a lot of different stock tickers (maybe around 1000). All my links are in the excel worksheet, so I am wanting to have it automatically go through, copy the link from the cell, paste it into the get external data from website feature, and return the results on a new worksheet for each query. I have little experience with VBE, but can kind of understand it. If there is any way to do this easily please let me know.

    Re: replacing text within a hyperlink with text from another cell


    I figured out how to change the part of the hyperlink i was trying to. I really shouldnt say hyperlink, because i was just trying to change the text part of the address so I could copy it and past it into "getting external data from the web." Now Im having problems with being able to get external data from web for more than one website using a macro. I want to have a macro so it will go through all the "hyperlinks" i have now created and use those to create a new worksheet through the Data function get external data from web. I hope that makes sense.

    I am attempting to replace a specific set of text within a hyperlink with text referenced from another cell. Also, I want to be able to do this accrost several cells.

    for instance:

    http://moneycentral.msn.com/investor/research/sreport.asp?Symbol=TICKER&FRK=1&Type=Equityhttp://"http://www.abc123.com/Ticker/graph"

    I want to be able to replace the phrase "TICKER" with the text from a cell above it. Doing this acrost a row, so that the hyperlink is in cell B2, and the work replacing "TICKER" is coming from B1. Then the hyperlink in C2 is replaced with text from C1....etc. A formula would be best, I have no experience with visual basic.

    Also, on another subject, is there anyway to automatically rename a worksheet after it has been created after getting info from external data, so it could possibly rename the worksheet with the above mentioned Ticker.

    I really appreciate any help.

    Thanks,
    Stephen