Excel Fun: Crypto Charts for Prices

in Excel For Alllast year

Hi fellow Hiveians and Excel enthusiasts!

Paula had shared a nifty post recently about some cool charts she put together for Bitcoin and I wanted to give it a try for me to do it with Litecoin. You can find the link to the original post here. If you like Excel but don't yet subscribe to the community, I would strongly recommend it! It's got a lot of awesome Excel tips, tricks and tutorials.

Stock Cryptocurrency Charts

To start, I wanted to remove the old fashioned name! I think it will remain though to the larger audience, kind of like we use old phrases for emails like CC which is carbon copy, something that's never done. I digress though!

So these charts were a lot of fun to make, and actually fairly easy and straight forward. I am using Litecoin as the coin of choice for me since that's what I like to buy when I can instead of Bitcoin. I do hold Bitcoin but it's a coin that I don't find has a lot of utility.


Interestingly enough, I don't see as many options as Paula does with my version of Excel. She's got one that's more up to date which is cool. I can't change my chart from those thick blocks to lines or something, I kind of prefer that to the blocks personally.

I then changed the dates on the column to be a better format, I didn't quite care for the 5-May-20 type, instead I made it 5/30/20 type to save on space. I also renamed it to be Litecoin HLC Chart to be a bit more intuitive, though I may rename that as well since it's an acronym and I dislike those when I am not familiar with what it represents.


OOPS I screwed that up!

LOL too funny. I am going to leave my mistakes there, above, but I realized I chose the wrong chart. I picked just a generic Waterfall chart instead of a Stock Chart. Now that I have the Stock Chart chosen, it's giving me all of the correct things! I repeated my steps above and adjusted as needed but I was able to make it look very similar to Paula's. These simple things will make a world of difference when you do this for work or clients! Always remember to double check your things before you chock it up to Excel sucking and not giving you the options you want hahaha. I totally blamed Excel that I didn't see all the cool things Paula did.


Opening High Low Close

The next chart I was playing around with was the Opening High Low Close chart. This chart is as it says but allows for more data points and options for formatting it. To get this, you need to select the columns Date Open High Low Close in that order so it formats the chart properly. Then go to the Waterfall chart dropdown and pick Open High Low Close Chart and it will bring it out for you.

I renamed the chart and added some colors to it. It was a bit tricky to add them but once I figured it out it worked out well!


To add colors, you can try to click on the datapoint if you are able to or you can click on one and then right click and Format Data. This will bring you to the section where you can choose what area you wish to modify.


As you can see I formatted the Up Bars in an orange, solid color. I repeated this same step with the down bar as well as the trend lines. When I wanted to insert a trend line, I clicked on the data point I wanted like High, I clicked on the top piece of the line and it knew I wanted the High's. I then right clicked on it and chose "Add Trendline" to have one inserted.


I then colored it and repeated that with the low and the open and close.

Personally, I find it a little too busy but I liked learning about the different options I could play around with.


Volume High Low Close

So the VHLC chart is pretty cool to look at. Seeing the volume in dollars of the overall trade for the day, high low and close all in one visually decent chart is pretty nice to me.


Volume Open High Low Close chart

Now for the final chart, but what was the original challenge! Making a chart that Paula shared a picture of but didn't tell what it was exactly. From the look of it, it's a Volume, Open, High, Low and Close chart. Let's get to making that one!


So now that we've got our chart set up, we need to do some modifications to it! I want to make it similar to the one above but to add some trend lines and adjust the axes a little bit.

I right click on the left axis because of how much space it takes up on the chart and want to reduce that to just 1 decimal place numbers since we are dealing with Litecoin instead of Bitcoin. This makes it look a bit visually easier to understand.

Now that we've taken care of that, the next step is to color those up and down blocks to make it look less awful. You want to click on the section I mentioned above where you can adjust the different ones just from the dropdown menu, making your life easier than trying to convince the mouse what you are trying to click on!

Once I took care of that, I wanted to add some trend lines to signify the high and low prices.

Finally, I changed the number columns for the left and right axes to be currency instead of number so it adds the dollar sign.


Voila! It now shows a similar type of visual look that Paula's does but with my own little spin on it and with Litecoin instead of Bitcoin.

Overall this was actually a lot of fun, I don't get to mess around with charts much in Excel, it's usually just using pivot tables and data manipulation that I've been doing.

Do any Excel work?

Let me know in the comments! I would love to hear your Excel stories on what you do to manipulate some data.

Connect with me!
Did you enjoy this post or my other posts? Please consider following me for more! @cmplxty

Do you want to get paid, in crypto, for searching the internet? Try using and signing up for Presearch to earn some great crypto! I've currently got 1,280 PRE tokens, with a market value of $17.93. It doesn't sound like a lot but when you search using sites like Google you get paid $0! Join Presearch to break Google's stranglehold on the internet searches. If you'd like to sign up, use my referral link below and spread the word!


Still using hive.blog? Upgrade! PeakD is a much better user-interface, gives loads more information at your fingertips and has some of the best developers behind it. Give it a try, go to www.peakd.com and see how much easier it is. You just need to use Hivesigner or Peaklock some of the most secure ways to log into other Hive frontends.


Shared on Twitter, #posh!

Congratulations @cmplxty! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You got more than 3500 replies. Your next target is to reach 3750 replies.

You can view your badges on your board And compare to others on the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Do not miss the last post from @hivebuzz:

Project Activity Update