Excel help

Discussion in 'General Discussion' started by Cheezdoodles, Apr 1, 2009.

  1. Cheezdoodles

    Veteran

    Joined:
    May 24, 2006
    Messages:
    3,930
    Likes Received:
    24
    I got price data time series for stocks in excel.

    Problem is that the data is downloaded from datastream, and this results in a desimal error (or rather, it forgets to put desimals into the number, the end result being that that instead of having a stock price of 12,12345 it shows as 12112345, meaning that the place where the , (or . depending on region) is supposed to be, is not there).

    Is there an easy way to make excel put a , behind the first 2 numbers in each time series?

    (cannot do it by hand, as its 2600+ observations for 20 different stocks)
     
  2. Phil

    Phil wipEout bastard
    Veteran

    Joined:
    Nov 19, 2002
    Messages:
    4,785
    Likes Received:
    377
    Location:
    127.0.0.1
    Hum, I don't have Excel installed at home, so can't test it, but you should be able to open the properties for the cell and change how numbers are displayed at. You should be able to pretty easily adjust the commas and how many of them should be displayed...
     
  3. Jawed

    Legend

    Joined:
    Oct 2, 2004
    Messages:
    10,873
    Likes Received:
    767
    Location:
    London
    Fill 2600 cells (in a column or row just like the original data) with the factor you want to multiply by, e.g. 0.000001. Then highlight your errant data, copy to clipboard, highlight first of the multiplication-factor cells, paste-special and specify the "multiply" option.

    Brute force for the win :razz:

    Jawed
     
  4. Cheezdoodles

    Veteran

    Joined:
    May 24, 2006
    Messages:
    3,930
    Likes Received:
    24
    I thought of this, but the problem is that the amount of numbers in each entry varies, so i cannot multiply by a constant factor to solve the problem.

    What would solve the problem, is if i could instruct excel to somehow put a , after the 2 first numbers in each entry. Is something like this doable?
     
    #4 Cheezdoodles, Apr 1, 2009
    Last edited by a moderator: Apr 1, 2009
  5. Jawed

    Legend

    Joined:
    Oct 2, 2004
    Messages:
    10,873
    Likes Received:
    767
    Location:
    London
    Some VBA:

    Code:
    Function FixDecimal(Bad As Long) As Double
     
    BadString = CStr(Bad)
    FixDecimal = CDbl(Left(BadString, 2) & "." & Mid(BadString, 3))
     
    End Function
    And call it in a set of 2600 cells, e.g. =FixDecimal(A1) in cell B1, etc.

    I'm reasonably sure the code must use "." and not your Windows-localised "," so don't edit that (unless I'm wrong, of course!).

    To create the function, hit Alt-F11 and in the editor choose Module from the Insert menu. Then paste that code there.

    Jawed
     
  6. Zaphod

    Zaphod Remember
    Veteran

    Joined:
    Aug 26, 2003
    Messages:
    2,207
    Likes Received:
    105
    Code:
    A1 123456789
    B1 =LEFT(A1;2)&","&MID(A1;3;9)
    
     
  7. Davros

    Legend

    Joined:
    Jun 7, 2004
    Messages:
    15,202
    Likes Received:
    2,491
    couldnt you just do format cells currency or number
     
  8. Jawed

    Legend

    Joined:
    Oct 2, 2004
    Messages:
    10,873
    Likes Received:
    767
    Location:
    London
    That just changes how they look, not what they mean.

    Jawed
     
  9. Cheezdoodles

    Veteran

    Joined:
    May 24, 2006
    Messages:
    3,930
    Likes Received:
    24
    Thanks a lot for all the help. Problem solved, and i saved a looot of time.
     
  10. Sxotty

    Veteran

    Joined:
    Dec 11, 2002
    Messages:
    4,970
    Likes Received:
    370
    Location:
    PA USA
    Dang Jawed that doesn't seem that hard.

    I have tons and tons of data that I might need to mess with in excel. I will have to look into using some VBA functions to deal with it. Previously I put stuff into matlab instead, but that has its own problems with large datasets as well.
     
  11. Jawed

    Legend

    Joined:
    Oct 2, 2004
    Messages:
    10,873
    Likes Received:
    767
    Location:
    London
    :lol: my circuitous solution has at least inspired someone to program within Excel :lol:

    Jawed
     
  12. Zaphod

    Zaphod Remember
    Veteran

    Joined:
    Aug 26, 2003
    Messages:
    2,207
    Likes Received:
    105
    Bastard! :twisted: Although, having had to do quite a bit of SAS lately, I almost long for VBA. That package is powerful, but man is it convoluted...
     
  13. Sxotty

    Veteran

    Joined:
    Dec 11, 2002
    Messages:
    4,970
    Likes Received:
    370
    Location:
    PA USA
    So I don't know if you guys could help me out on my problem, at least the proper words to search for in google.

    Should I be looking for visual basic tutorials or excel macro tutorials?


    My problem is actually fairly simple from a coding perspective.

    What I have is a bunch of data from which I need to pull certain values. The location of the values is related to the step and test cycle number.

    What I need is a function that searches through the step number column and when it changes from say step 12 --> a higher number it goes over two columns and copies a value. It will have to do this repeatedly in the same column so it is not a one time thing. I have found plenty of examples in searching for a specific value, but not for comparative values. Is there a better search term I should use in google?
     
  14. Jawed

    Legend

    Joined:
    Oct 2, 2004
    Messages:
    10,873
    Likes Received:
    767
    Location:
    London
  15. Sxotty

    Veteran

    Joined:
    Dec 11, 2002
    Messages:
    4,970
    Likes Received:
    370
    Location:
    PA USA
    What I want is to find when the value in a specific column turns from 12--> a number >12

    Usually it goes step 12-->13, but sometimes to 14

    So the code in generic language is

    Code:
    for x=0:max row
    if Cell(x,column)==12 and Cell(x+1,column)>12
     
    set activecell(x,column)
    ActiveCell.Offset(2, 1).Select
    copy that cell then write it to a new worksheet in a specific row column position
    
    end if
    end
    
    Obviously I could probably just set the active cell to the one I want based on the offset already.

    It is more a question of learning the basic structure of VBA in comparison to other languages I am usd to I guess.

    That site though looks good it has some nice intro material to use. I just need the simple stuff like to ensure I am selecting the right cells and so forth.
     
  16. Jawed

    Legend

    Joined:
    Oct 2, 2004
    Messages:
    10,873
    Likes Received:
    767
    Location:
    London
    So the concepts you should investigate are:
    • for each - allows you to iterate through the cells in a range
    • activesheet - worksheet where the function was called from
    • worksheets - access worksheets in the current workbook, e.g. by name (methods include adding a new worksheet)
    • with - useful for de-cluttering long-winded object references
    That list goes beyond what you need for this function but it'll get you right to the heart of using VBA in Excel.

    Jawed
     
  17. Sxotty

    Veteran

    Joined:
    Dec 11, 2002
    Messages:
    4,970
    Likes Received:
    370
    Location:
    PA USA
    Thanks a lot. I will look that over later and let you know if I find success after a couple days.
     
  18. Arwin

    Arwin Now Officially a Top 10 Poster
    Moderator Legend

    Joined:
    May 17, 2006
    Messages:
    17,706
    Likes Received:
    1,216
    Location:
    Maastricht, The Netherlands
    Don't want to discourage you from programming at all of course, but I think that the highlighting can fairly easily be done with the conditional formatting option.
     
  19. Simon F

    Simon F Tea maker
    Moderator Veteran

    Joined:
    Feb 8, 2002
    Messages:
    4,560
    Likes Received:
    157
    Location:
    In the Island of Sodor, where the steam trains lie
    I haven't really been following the thread, but could you do something like have a column of the differences which are made into a step function, ie. if(XA-1 != XA, 1, 0), and then use SUM(start_of_column, CurrentPos) to create a column of index values?
     
  20. Sxotty

    Veteran

    Joined:
    Dec 11, 2002
    Messages:
    4,970
    Likes Received:
    370
    Location:
    PA USA
    I actually have been having pretty good success with it. And it is a bit more complicated than I let on, but what I said was the basics as I needed them. I haven't really coded in VB, VBA, or even VC and the syntax is a little different than borland c++, or matlab which is what I am used to.

    So far I have the code doing almost everything I need. It is missing one things.

    I need to make it a portable macro. I made sub routines that tested everything so I could figure out how to create a worksheet, read a specific value, write a specific value etc.. then I combined all of them into a final subroutine. I think I need to make it into a function and save it as XLA and delete the worksheet or something along those lines. I will have to make a number of other programs as well since there are four unique test procedures. Or I could have a message box that prompts the user to select what type of test it was and select the correct method to decode it.
     
    #20 Sxotty, Apr 9, 2009
    Last edited by a moderator: Apr 9, 2009
Loading...

Share This Page

  • About Us

    Beyond3D has been around for over a decade and prides itself on being the best place on the web for in-depth, technically-driven discussion and analysis of 3D graphics hardware. If you love pixels and transistors, you've come to the right place!

    Beyond3D is proudly published by GPU Tools Ltd.
Loading...