+ Reply to Thread
Results 1 to 16 of 16

Move data daily into new column

  1. #1
    Registered User
    Join Date
    07-08-2007
    Posts
    28

    Move data daily into new column

    Hello,

    I have used the "Import External Data-Web Query" to gather financial data.
    This data is updated daily by the web site. The data fills up columns A2 to E 6000.
    The data on Column B is of importance and I need it to be stored daily. I need a code that will store todays Column B data in column F, tomorrows Column B data in Column G, dayafter's column B data in Column H and so on..

    In short, I need to create a database automatically..

    Please help me...thanks

    Regards,
    RButhello

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi there,

    This little routine will copy the entire column B to the first empty column on the right-hand side of a worksheet. Just enter it into a standard VBA code module in your workbook.

    Please Login or Register  to view this content.
    Hope this helps. Please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Thanks a lot Greg,

    I am new to programming, so pardon me if I am wrong.
    The code that you have put up needs a button to execute right.

    My requirement is:
    1. The data from column B must move to the next empty column automatically daily. To help you in this you may use column E. Column E downloads the date at which column B data is received.

    2. Some of the schemes(Column A) may be revoked a few days later, or some new ones may be introduced. Hence If I use your previous code to move column B, the new data may not match against the previous days data.
    I tried to solve this particular problem by using VLOOKUP(), but I am unable to think of any logic that will help me do the same. I tried the following formula but it does not help my record maintaining requirements:
    =IF(H$1=$E6,VLOOKUP($G6,$A:$D,3,0),"")

    I have attached a zip file of the excel sheet. I hope I have explained my problem well.....

    Also is it possible to get this done (execute the code) even if I do not open the workbook??


    Regards,
    RButhello
    Attached Files Attached Files

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again RB,

    Thanks for the example you posted.

    I think the simplest way of executing the code for this application is to provide a "Copy Data" button on the worksheet. If you feel it's necessary, I can probably provide a feature which disables the button once the Copy operation for a particular day has been performed - this would avoid any problems associated with multiple copies being made accidentally.

    Now I just want to check that I've understood your requirements correctly.

    It seems that columns A:E contain daily data and that you want the columns from G onwards to contain historical data, BUT the number and/or details of the records in the daily data is not constant. As a simple example, we start off today with five records, but next week "Birla Sun Life Mutual Fund" is dropped from the list and we begin to track "Canara Robeco Cigo-Income Plan" and "DBS Chola Triple Ace-Regular Bonus" instead. This means that the number of records in our historical data increases from five to seven, and the "row-for-row" correspondence between our daily data and our historical data is no longer maintained.

    If my understanding is correct, we're probably talking about a two-stage process - first we transfer the daily data to the historical data using a VLOOKUP process and then we just do a Copy & Paste Values operation on the "looked-up" data. This shouldn't be too difficult to implement.

    Can you please let me know if I've understood things correctly?

    Regards,

    Greg M

  5. #5
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hello Greg M,

    Yes, you have understood my problem perfectly....

    I would'nt mind the button to pick the data once a day. However, is it possible to perform the same function automatically since, columnE has the present date.

    Thanks....

    Regards,
    RButhello

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi RB,

    I suggest we use a button to implement the data transfer. It's possible to do it automatically, but then we have to ask what "automatically" means.

    1. When the workbook is opened? If so, the transfer will be performed before today's data have been inserted.

    2. When the workbook is closed? Certainly possible, but then anything "strange" won't be obvious until the next time the workbook is opened, and mightn't even be noticed at that stage.

    3. At a particular time of the day? Possible, but to date my experience of the OnTime method hasn't been encouraging.

    Feel free to suggest something else and I'll see what I can do.

    In the meantime, I think we're making progress!

    The code in the attached workbook seems to do what we're after. Basically, a VLOOKUP formula is entered in each cell of the UsedRange in the first empty right-hand column - this displays the daily data in that column. Then we do a Paste Values operation on that column - this ensures that the data are now "fixed" and won't change if any Schemes are added/deleted/modified.

    I'm assuming that when Schemes are added/deleted, all the daily data in Columns A:E are first deleted, Schemes that will no longer appear in the daily data REMAIN in the historical data, and any new Schemes are inserted in the appropriate rows in Column G. The new daily data are then imported to Columns A:E.

    One final point which you might find confusing if you haven't encountered it before, although you won't find it at all if you don't look hard for it! The application uses a User-Defined Function called NetAssetValue. This particular formula is implemented as a Relative Named Range and (if you're interested) you can find it by using Insert > Name > Define. Click on NetAssetValue in the dialog box and the formula will appear in the Refers To box. Relative Named Ranges can be very useful, but what they do can be easily (although perhaps not as neatly) done by normal formulas.

    Anyway, I hope this helps and please let me know how you get on.

    Best regards,

    Greg M
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hello Greg M,

    Excellent code, thanks a milliion.
    I will go by your suggestion to use a button.

    I want to know, why you made a seperate name "NetAssetValu" instead of using the formula in the main code.

    I need to clarify something.
    Your code has provisions to take care of schemes that have been stoped. Buth there is no provision for adding new schemes.
    Just as old schemes are stoped certain new ones may be started in a different name. I need such new schemes to be added to the last entry of ColumnG.
    I have found a code for finding duplicates but I dont know how to modify it for finding new entries...


    Dim MatchBarCode As Range



    'first check column A for dups
    Set MatchBarCode = Range("G1", Range("G65536").End(xlUp)) _
    . Find(Range(NEW SCHEMES), LookIn:=xlValue, MatchCase:=True, lookat:=xlWhole)

    If Not MatchBarCode Is Nothing Then Exit Sub

    'add new scheme
    Application.EnableEvents = False
    Cells(Range("G65536").End(xlUp).Row + 1, 1) = Range(NEW SCHEME)
    Application.EnableEvents = True



    Please help....

    Thanks.

    Regards,
    RButhello

    P.S.: I made a few coments in your code for my understanding (since I am new to excel programming). Please run through it to see if I have understood it.....
    Attached Files Attached Files

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again RB,

    Glad to see that we're making progress.

    Judging from the comments you've made in my code, you understand it completely. Regarding the very last comment, there certainly IS a reason (maybe not a very important one, but nevertheless...) - without the last action (which I refer to as "parking the cursor") the range on which you've just performed the Paste Special operation will remain selected.

    Regarding the NetAssetValue formula, I like to use Relative Named Ranges when I'm dealing with a formula which will be copied across rows or down columns. As I mentioned, they do exactly the same sorts of things as "normal" user-defined functions, but I think they're a bit neater.

    Now, maybe I've misunderstood how you enter new schemes

    I'm assuming that when Schemes are added/deleted, all the daily data in Columns A:E are first deleted, Schemes that will no longer appear in the daily data REMAIN in the historical data, and any new Schemes are inserted in the appropriate rows in Column G. The new daily data are then imported to Columns A:E.
    Your latest post suggests that new schemes simply replace existing schemes in the daily data set, and that you need some way to check that all schemes in the daily data set either exist already in the historical data set, or are added at the bottom of the historical data set if they don't. If this is what happens it should be possible to include an "Add to Historical Data" button which performs this check. Is this what you need? Do the schemes in the historical data set need to be sorted in any way, or is this done manually?

    Regards,

    Greg M

  9. #9
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hello Greg M,

    Thanks for going through my comments.


    For the added/deleted schemes I shall explain the situation:

    The issue with Columns A:E is that data is replaced dialy. I have used the following to achieve it:
    Data -> Import External Data -> New Web query.
    I then gave it a link to amfiindia.com. In it, I went to the required page and selected the required table. This table populates Column A:E.

    Now the issue is that amfiindia.com will blindly add or delete new schemes into the list. My requirement is to automatically update existing schemes and also append new schemes to the list.

    I hope I am able to clear the issue.....

    By the way, I am curious to know, what line of work are you into?? Are you a full time programmer???

    Thanks....

    Regards,
    RButhello

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again RB,

    The attached workbook incorporates an "Update List" feature which scans all schemes in the daily data set, checks that each one exists in the historical data set, and if not, adds the missing schemes at the end of the historical data set.

    So, the "daily routine" should be:

    1. Import the day's data via Query Refresh
    2. Press the Update List button to add any new entries to the historical data set
    3. Press the Copy Data to copy the daily data to the historical data set.

    I've included a few comments in the new code to give you an idea of what's happening.

    By the way, I noticed that you'd altered the text of the "Data for ... have already been copied" message. Just for information, when I worked in the European Statistical Office, "Data" was always considered to be a plural noun. (Sorry, just being pedantic )

    Hope the above helps - as usual, please let me know how you get on.

    Regarding my line of work, I'm a professional electrical engineer - High Voltage substations, nuclear fusion research, and just a few years in energy statistics. I'm not a full-time programmer by any stretch of the imagination, but I get a buzz out of making computers do what they were designed for, i.e. to make life easier for us poor humans.


    Best regards,

    Greg
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hello Greg M,

    I think you sent me a wrong workbook....
    cause I cant seem to find the required code in it....

    "By the way, I noticed that you'd altered the text of the "Data for ... have already been copied" message. Just for information, when I worked in the European Statistical Office, "Data" was always considered to be a plural noun. (Sorry, just being pedantic )"....... hehehe..... I can't believe you noticed the alteration.....good observation. I shall make note of it...

    Electrical Engineer, NUCLEAR FUSION RESERACH...very impressive.

    Regards,
    RButhello

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi RB,

    Sorry about that - and it took me I-don't-know-how-many attempts to upload it!!!

    I'm attaching the intended version this time.

    Best regards,

    Greg M


    P.S. After trying for literally two hours (same as last night!) to upload a 43kB file I think I'm going to have to abandon this attempt. Can you give me an e-mail address to which I can send the file?

  13. #13
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hello Greg M,

    Thanks for the effort.

    You may mail me the excel sheet either at

    [email protected] or at

    [email protected]

    Regards,
    RButhello

  14. #14
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi RB,

    I FINALLY got that file to upload. I've probably spent more time trying to upload it than I did writing it.

    Regards,

    Greg M
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-08-2007
    Posts
    28
    Hello Greg M,

    Thanks a million, it works like a charm...

    I plan to use the "Update List" code at the begining of the "Copy Data" code.
    In this way I will need just one button.


    Anyway, thanks once again...(for the upload bit...)


    Regards,
    RButhello

  16. #16
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi RB,

    Ok on all of that.

    Just one point - by all means have a single operation (button) for the USER, but my advice would be to keep the two procedures separate for the PROGRAMMER. You can combine the operations using the following code:

    Please Login or Register  to view this content.
    THIS is the code which should be called from the User button.

    The two procedures perform functions which are quite different, and good programming practice would be to maintain them as separate procedures. This will make life easier in the future if you need to make changes to the application. The "megaprocedure" can look impressive, but it can be a bit unwieldy for maintaining and/or upgrading.

    Anyway, many thanks for all the feedback that you've given me - I'm very pleased that I've been able to help.

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1