+ Reply to Thread
Results 1 to 9 of 9

Visualization of data with tails

  1. #1
    Registered User
    Join Date
    07-10-2021
    Location
    london
    MS-Off Ver
    latest
    Posts
    6

    Visualization of data with tails

    Rotation Matrix screenshot latest.png

    I have some data which i have made into a scatter chart with a tail.

    In the case of the data you can see in the attached image, there is TODAYS data, and 2 other days of data. What function could i use so that i have a little dropdown menu and can select a number such as 3, 5, 10, 20 etc and then the chart updates to select 3,5,10 or 20days of data from my data to update the visualisation?

    Also, is there a way to create quadrants on this image, and specify where the lines start and finish?

    Many thanks in advance

    Mark

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,406

    Re: Visualization of data with tails

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-10-2021
    Location
    london
    MS-Off Ver
    latest
    Posts
    6

    Re: Visualization of data with tails

    On the attached excel file, I HAVE plotted all 4 data sets on a scatter plot with a tail
    So I have included 12/07/2021, 08/07/2021, and 07/07/2021

    If I plotted more on one chart (for example 20 or more dates with many securities) it will start to look like spaghetti.

    My question is how can I create a drop down or sort of toggle so I can press up or
    down on arrow key and then it would move the dates (3 at a time or however many i want), so we can see those arrows move on the chart as selected range moves on the table?

    for example I would click down arrow and then we could see 08/07/2021, 07/07/2021
    and 06/07/2021, instead of 12/07/2021, 08/07/2021, and 07/07/2021 as we do now?


    How do i upload an excel spreadsheet?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-10-2021
    Location
    london
    MS-Off Ver
    latest
    Posts
    6

    Re: Visualization of data with tails

    Hi Richard, ok i did it! Many thanks in advance
    Best
    Mark

  5. #5
    Registered User
    Join Date
    07-10-2021
    Location
    london
    MS-Off Ver
    latest
    Posts
    6

    Re: Visualization of data with tails

    Hi Richard
    If you see RRG charts on the stockchart website (I'm not being allowed to post the URL here)
    and click animate on the RRG charts and then you will see how it automates running through the history of stocks versus one another.
    I didn't want to automate it so much as they do, but I'd like to be able to do that in a controlled way going through dates by toggling up and down
    Best
    Mark

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,341

    Re: Visualization of data with tails

    If I found the correct page, so I can post the link for you: https://stockcharts.com/freecharts/rrg/

    How closely are you required to match stockchart's user interface? In my experience, Excel's chart engine does not readily provide some of those fancy UI controls (like sliders and arrows and such). If you are required to match stockchart's UI, it might be easier to use a third party charting engine rather than Excel.

    If I understand what you are trying to do, what I see is that you want to chart "all" of the data, then filter the source data so only some of the data is shown on the chart. In my older version of Excel editing your file, I:

    1) Selected each data series in the chart and extended it down to include the entire column of data. Format the chart so that does NOT move or size with cells.
    2) enter "date" into A3.
    3) Selected A3:I51 -> Data -> Filter to turn on autofilter.
    4) Click on the dropdown arrow for the date field and chose the desired filter criteria.

    At this point, I can use the tools in the filter interface to display whatever rows I want in the chart. Granted, the UI is quite different, but it seems to perform the same task.

    As I see it, the big question at this point is understanding exactly what kind of UI you want for this spreadsheet, so we can consider what UI elements Excel supports and talk about how those might be incorporated into this "chart a filtered subset of the data" kind of task.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    07-10-2021
    Location
    london
    MS-Off Ver
    latest
    Posts
    6

    Re: Visualization of data with tails

    Hi Mr Shorty
    Yes the link you posted is correct (I still cannot post or repost).
    I do not need to match the stockcharts interface at all. I do not want to complicate my life by using a 3rd party.

    I just simply want to be able to take lets say 3 data points for each stock (let's say that represents 3 days or the points on the chart) and then let's say that i am plotting BDRY etf and QQQ etf on that chart from December 01. On the chart i can see Dec 1,2,3 on the chart with an arrow on Dec 3rd to show the direction at that point in time....

    Then i just want to be able to easily select one day forward, and then another day forward so its always rolling forward with the last 3 days, i.e. Dec 2nd, 3,4th, then Dec 3rd,4,5th......so i would be able to see the data moving around on that chart like a little worm with an arrow....and i can see how it evolved until now.

    Is that possible?

    I didn't understand how to select data series and format the chart so that does NOT move or size with cells.
    Also i didnt understand about turning on autofilter.


    Is it possible to show me in my excel spreadsheet?

    Best regards
    Mark

  8. #8
    Registered User
    Join Date
    07-10-2021
    Location
    london
    MS-Off Ver
    latest
    Posts
    6

    Re: Visualization of data with tails

    Actually i tried your suggestion of highlighting the data in cells DI 2 to DN 51 which is SPY,
    SLY, QQQ stocks
    Then i clicked on filter which gives me the little drop down arrows.

    Then i changed the data to highlight just, say, February data....then the table disappears to
    just the month or months i try, but the chart remains the same, or just completely disappears.

    First, i'd like to try to get this working so i can see how it works.

    Second, i'd like to ask if there is a less cumbersome way of doing it?
    I mean currently if it worked, i would have to click the down arrow and keep clicking and
    adjusting....id really rather just be able to tap my down or up or side arrow and then for the
    date to adjust on a rolling basis like i explained in my previous message.

    What do you think?

    Best
    Mark
    Attached Images Attached Images

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,341

    Re: Visualization of data with tails

    If the chart is adjacent to the table and disappears when you filter, that is probably because you have the chart formatted to move or size with cells. If you are having trouble finding the "move or size with cells" options, try this help file: https://support.microsoft.com/en-us/...1-1d0788ea783f

    If you filter data and the chart remains unchanged, that suggests that you have the "hidden and empty" cell setting set to display hidden cells. Find the hidden and empty cell settings and make sure it is set to not display hidden cells/data. Another help file: https://support.microsoft.com/en-us/...b-9ca49cb92274

    I have seen spreadsheet with sliders and arrows and various other activex type controls. I have never used them personally, but here's a help file that introduces some of what you can do with them: https://support.microsoft.com/en-us/...0-ffb9ab94e7c2

    At this point, I would suggest that you explore what the filter can do to be sure that what you want can be accurately represented as a "chart filtered data" kind of operation. Once we establish that this is what you want to do, then those who are more familiar with alternative UI tools or the FILTER() function will be able to enter the discussion and talk about different ways and tools for interacting with your data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data visualization for roulette results (using Datapoint app)
    By Kyewilkinson in forum Excel General
    Replies: 3
    Last Post: 09-01-2021, 05:24 AM
  2. Data management and visualization enthusiast
    By J. Holbrook in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-09-2020, 11:21 AM
  3. [SOLVED] Horizontal to Vertical Data Visualization
    By zeeshan.haque in forum Excel General
    Replies: 13
    Last Post: 07-12-2019, 07:47 AM
  4. PowerPivot 2016: Help with data visualization using pivot chart
    By jlt199 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-02-2018, 01:37 PM
  5. [SOLVED] Heads and Tails Macro Separate Column for Head; Separate Column For Tails
    By rmccain in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2016, 05:11 AM
  6. data visualization
    By utefan001 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-01-2005, 12:05 PM
  7. data visualization
    By utefan001@gmail.com in forum Excel General
    Replies: 1
    Last Post: 05-31-2005, 11:05 PM

Tags for this Thread

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