Locate Word In Column & Copy Adjacent Range

  • [INDENT]Hi, I am new to VBA and have tried to develop the code for finding a specific stock symbol (Column A) from over 4200 symbols from a downloaded csv file, copy the data in in its (specific symbol's) row (in the next 5 columns B to F) to the 3rd blank row in the master workbook (data arranged from Bottom to Top). In the Master workbook each stock symbol has a worksheet with its symbol as the tab e.g. BHP.AX is the tab or sheetname for BHP stock in the Australian Stock Exchange. The downloaded file comes from a subscribed site EODData which provides daily OHLCV (Open/High/Low/Close/Volume) data against each symbol.
    My attempt is shown below and it is very primitive. It does not work! I wish to run it from the Master workbook (name:-0PortfolioASXMultipleIB.xlsm) and not from Csv Data file. Please help

    [/INDENT]

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi Wkurukl


    Something like the following might set you on your way. Look out for the sheet name. Also how do you know what stock symbol to search for? Do you want to search for all of the symbols in your master workbook and will you be using the sheet name as the prompt for this? Anyways this will open your csv file and bring found data back to the 3rd row of sheet 2. I have used the word "Start" as the item to find but you will probably want the procedure to be a bit more dynamic.


    Take care


    Smallman


  • Re: Locate Word In Column & Copy Adjacent Range


    Thank you for your kind response.
    The Master workbook contains the worksheets for stock symbols (Tab name is the symbol e.g.AGK.AX is the sheetname) that I trade in (eg. AGK.AX for AGL Energy Ltd; BHP.AX for BHP Limited; RIO.AX for Rio Tinto Limited etc.). The CSV Data file also contains the same symbols such as AGK.AX, BHP.AX, RIO.AX. I copied the tab name to A1 and tried to use it as String to search in the csv data file, but could not get it to work. I want to update each worksheet individually and anlyse each separately. It is also good if all the worksheets are update in one go. I must run the macro from master worksheet.
    I will try out your code and report back.
    Thank you once again.

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi


    Here is the files I used to test it. Mmm I can't upload my csv file. Just create one, change the path where you save it though. Those stocks are Aussie stocks. Are you down under?


    Take care


    Smallman

  • Re: Locate Word In Column & Copy Adjacent Range


    Symbol Date Open High Low Close Volume
    AAA.AX 10-May-12 50.09 50.09 50.09 50.09 1150
    AAC.AX 10-May-12 1.175 1.23 1.17 1.2 464812
    AAD.AX 10-May-12 1.26 1.35 1.26 1.34 2686057
    AAE.AX 10-May-12 0.008 0.008 0.008 0.008 178088
    AAI.AX 10-May-12 8.87 8.87 8.87 8.87 0
    AAJ.AX 10-May-12 0.07 0.07 0.07 0.07 0
    AAK.AX 10-May-12 0.002 0.002 0.002 0.002 0


    Yes I am in Melbourne. I noticed you are in Brisbane. Good to know you.
    The above file a small part of the csv file (ASX_20120510.csv) is downloaded from a subscribed website EODData. I only use the data from Column B to G for analysis.
    My master Workbook contains only a few selected stocks with AAJ.AX as the Tab name for example.
    The Master workbook needs to be updated daily with End Of Day (EOD) data for charting and calculating RSI and other indicators.
    Hope this explanation will help you to help me.
    Kind regards
    wkurukul

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi, Smallman
    I download stock data from Yahoo Finance in Date/Open/High/Low/Close/Volume format and use various formulae for stock market analysis, both fundamental & technical.
    The data given by Yahoo finance is delayed by one to two days and are sometimes inaccurate.
    EODData website goves daily stock market figures soon after the end of day. This is the reason for the above project. Please help
    Thanks and kind regards
    wkurukul

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi wkurukul


    Melbourne, nice spot!!! I am going to be there in a couple of months to watch a fair bit of football.


    Where in the Master workbook are the symbols kept. If you could upload a cut down version of your Master workbook showing where the symbols are coming from (how you want to tell vb where to look). I expect this in the form of a list somewhere. I should be able to help out.


    Take care


    Smallman

  • Re: Locate Word In Column & Copy Adjacent Range


    HI,
    Not sure whether I uploaded correctly
    Worksheet names are found only on the sheettabs and if necessary tab name can be copied to A1 (currently an empty cell) cell using code.The new code should insert row at A3 and copy the latest data to that row. After that I can use Analyzerxl software to do the technical analysis.
    Cheers
    wkurukul
    PS. Let's meet for coffee when you are in Melb

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi Wkurukul



    Sorry for the delay getting back to you, the sand man hit me hard last night. OK i have had a look at your file, thanks so much for posting it as it makes it pretty straight forward when you can see the structure. The following will open a csv file and uses the BulkQuotesXL Settings tab to get the quotes. Do the tickers these days really suffixed by ax. I assume that is short for the ASX, the exchange the stocks trade on? So if the stock code is only BHP for example we might need to change this procedure slightly as it assumes the names in Col A of the above sheet are the Unique identifiers.


    Anyways here is the code and the file. I can see some questions but these are all ones which if you have not asked will come to you. For instance, what if you want to add additional socks? What if the sheet doesn't exist but the stock is in the BulkQuotesXL tab? I have tested this using a csv file on my computer seems to run fine.


    Coffee is the magic word in my world everyday. I won't hold you to it but if you do feel so inclined to ply me with coffee one morning I will happily oblige.



    Take it easy


    Smallman


  • Re: Locate Word In Column & Copy Adjacent Range


    Hi, Smallman
    Thank you very much for this code. It seems to be going up to the last loop.
    Two issues
    (1) Run-time error '91' at find.offset(, 1).Resize(, 6).Copy twb.Sheets(str).Range("A3")
    (2) I will have to change everyday the code at two different places in the code
    sPath = "C:\Users\Wilfred\Documents\EODData\DataClient\ASCII\ASX\ASX_20120517.csv"
    sFil = Dir(sPath & "ASX_20120517.csv")
    Will you be able to help me solve these two issues.
    Kind regards
    wkurukul

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi Wkurukul


    I don't get those issues. As I said I tested it on my machine and it worked fine, through all the loops. You will have to upload the version you are using for me to help. I have to see your set up to see where you have an issue. Your set up must be different to mine.


    Take care


    Smallman

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi,
    Ticker suffix varies from website to website. Google Finance uses ASX:BHP whereas Yahoo Finance uses BHP:AX. Fortunately EODData website also uses BHP.AX and therefore there is no issue here. The csv data file changes its name everyday from for example ASX_20120517.csv to ASX_20120518.csv

  • Re: Locate Word In Column & Copy Adjacent Range


    The data get copied to Master Workbook, but macro hangs at
    The Run-time error '91': says
    [Object variable or With block variable not set.]
    Does this make any sense?
    Cheers
    wkurukul

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi Wkurkul


    Once again not without seeing the file. It works for me. Here is the file it works on. Save this to your hard drive and run a test on it and you will see what I see. If you want me to see what you see zip and upload one of those raw files of yours. I can't help you if you don't do this.


    Ah I have had an idea. The reason it is bugging out for you is because it can't find the stock name. I have set up a perfect environment where all the codes are covered. Maybe you have not done this? May have to build in some error trapping for the no find scenario. I would prefer t see you set your spreadsheet up so this did not occur.


    I could not upload a csv file so I uploaded it in Winzip. This is only a 1k file with all your stocks on it.



    Take care


    Smallman

  • Re: Locate Word In Column & Copy Adjacent Range


    Hi,
    You are right. I need a lookup table or Input message box to find the correct daily csvdata file to download from. P
    lease help me with this lookup code incorpoarted into your existing code?
    Thanks. Will contribute towards your sponsorship of Injured Royal Marines for your effort.
    KInd regards
    Wilfred

  • Re: Locate Word In Column & Copy Adjacent Range


    Also, The copied data is not in the same format as the old data. Date format should be dd/mm/yyyy and the OHLC prices should be 42.50 instead of 42.5 for example. Volume should be 123890 without decimals.
    Thanks and kind regards
    wkurukul