May 22, 2013  Works with versions 2010/2007. You can import the data into a detailed spreadsheet in Excel which can be kept up to date. Also includes instructions on getting currency exchange rates. If this works for you, check out my post: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II 4) Use Google instead This probably means that for regular users Yahoo Finance is no longer a viable option to obtain stock quotes in Excel.

• • • • • • • • PLEASE FULLY READ THE SIDE-BAR BEFORE SUBMITTING A POST! SUBMISSION GUIDELINES FAQ PROBLEM SOLVED? OPs can reply to any solutions with: This will award the user a and change the flair to SOLVED. TO APPLY CODE FORMATTING Use 4 spaces to start each line You can select code in your VBA window, press Tab, then copy and paste into your thread or comment. PROVIDE YOUR DATA! Include a or use the website to generate the reddit table markup from your spreadsheet.

Recent ClippyPoint Milestones! Congratulations and thank you to these contributors Date User CP 25 10 10 10 10 10 1 25 500 25 A community since March 11, 2009 Download the official to convert Excel cells into a reddit table.

Modern Excel can grab information from multiple files in a single folder. Just open the worksheet, refresh the query and it’s up to date! This article is a condensed version of just one chapter in – the new 2 nd edition has a lot more detail and example files ready to use. You don’t have to open a list and graft on the new records. Games for mac. Just drop a new data file into a folder and Excel does the rest.

Or use the same method to update sales information, sport scores or any situation where there’s new data arriving. Here’s an example with the Microsoft stock price in the 21 st Century. We’ll setup the price history for past years then easily add more recent information. Then we’ll use the same query for other stocks. Get and Transform Start by getting some data in a form Excel can read and put them in a separate folder. In this case, two.csv files with MSFT prices from 2000 to 2017.

You might think you need to import each of those.csv files individually but Excel has a way to grab those two and any other data files in that folder. Put into Excel Now open Excel and look on the Data Get and Transform Get Data from File. Down the bottom of the fly-out list is ‘From Folder’. First, fairly obviously, point to the folder where the data files are. Then see the data files available in that folder. Choose Edit to continue.

Next is the full PowerQuery editor but with a twist. The two data sources are visible. The first column is called ‘Content’ as ‘binary’.

That might seem strange because the content is really a plain text file (.csv), but don’t worry about that. Combine The real magic is the little double-arrow icon in the content column. That tiny, tiny button combines the listed files and a whole lot else besides.

You can preview what Excel gets from each data file and, if necessary, change the origin type and delimiter. Once you’re happy with the settings, click OK. When you’re back at the main PowerQuery, look at the applied steps pane. Before clicking the little ‘Combine’ button there was only a single step, now there’s six more. On the left, there’s a lot more queries listed.

The main one you want is the ‘Other query’ at the bottom. Centurylink modem setup. That’s the query with the combined list of data from both the.csv files. Adding more information That’s not the end of the story.

So far, we’ve just got price data until 2017, lets add prices for the first quarter of 2018. The new data file is in the same format as the other two. The name of the file doesn’t matter in this example. Once the new data file is in the folder, Refresh the query. Excel will rescan the folder, import the new data file and produce a new data table. Then refresh the PivotTable/PivotCharts to see revised results.

We’ve added two more data files, one for each month just to show that the data doesn’t have to be in specific ranges. We could add monthly, weekly or even daily data files. As long as each file has the same data format, you’ll be OK. Now the refreshed PivotTable shows the extra months as well. But wait, there’s more has a whole chapter on the ‘from File’ option in Excel 2016 with many more tips and advice.

• Adding quick checks to ensure the data is complete. • Choosing which data files to select from the folder. • Copying the query to work for other stocks • Converting incoming data to suit your Excel needs.

• Adding calculated columns to speed up analysis. • Suggested free sources for historical price data.

Also has a detailed look at the upcoming Stock data type in Excel 2016/Office 365. For the first time, Excel can directly grab stock prices and other company info without messing about with data feeds.