Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Historic ohlc prices in excel - fast and easy

Discussions regarding financial software
eventide
2 Lemon pips
Posts: 102
Joined: October 24th, 2017, 3:29 pm
Has thanked: 3 times
Been thanked: 83 times

Historic ohlc prices in excel - fast and easy

#307625

Postby eventide » May 11th, 2020, 2:26 pm

Heres an excel file which uses the beautiful dynamic range feature of excel to present ohlc prices for a specified period. It's nicer than having to go and download a .csv from yahoo finance or wherever. Try it out on a few tickers. Macros will need to enabled.

Dynamic arrays are available in Office 365 only, not excel 2019 or before as far as I am aware.
If you're using an earlier version you can make it work using array formulas.

All you vba types on here can have some fun figuring out how it works too. I've done my best to make it a challenge.

https://www.dropbox.com/s/kyvz4s0urs0avxl/hist.xlsm?dl=0

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: Historic ohlc prices in excel - fast and easy

#309752

Postby kiloran » May 18th, 2020, 8:18 pm

Many thanks for this Eventide, it's good to get new contributions.
I'll try to have a look at it, but I'm way behind Excel 365..... I'm on Excel 2010, and I think we have some users on Excel 2000!

--kiloran

eventide
2 Lemon pips
Posts: 102
Joined: October 24th, 2017, 3:29 pm
Has thanked: 3 times
Been thanked: 83 times

Re: Historic ohlc prices in excel - fast and easy

#309874

Postby eventide » May 19th, 2020, 9:52 am

You probably know this, but then you can just expand the function from a single cell to a range which is 5 rows wide and as many deep as days you are seeking the data for, and use ctrl-alt-enter (instead of just enter) to set the array formula.

I've resaved the file with an example of this on the RHS.


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 11 guests