office 365
6204 TopicsStock Data Types Wont Refresh - Provides Erroneous Error Message
I have an Excel workbook I created a few months ago and this afternoon while I was away, the "stocks" data type refresh feature stopped refreshing. The error message I receive instructs me to sign in to an account associated with this product (Excel), but I am clearly signed in. To be sure, I sign out and sign back in. Hit 'refresh' and receive the same error message. MS tech support tried to make a manual repair during a chat session, but it still will not refresh. Any thoughts? Thanks in advance.32KViews18likes195CommentsExcel selects wrong cell
Hello, I am here to report on what seems to be a common issue... After working for a few minutes in excel, when I go to click on a cell, the wrong cell actually gets selected, and it is usually several cells above or below the cell my mouse is hovering over. I sometimes have success in saving, closing and reopening the book, but not always. Excel Office 365 version, updated on schedule, as always.53KViews13likes66CommentsExcel Online - All Sheets Menu Option Greyed out.
Hello - I have an issue where the Excel Online is showing the All Sheets Menu greyed out for all excel sheets. This was not an issue yesterday. The only way Ive found to turn it back on was to Activate Viewing Mode, then flip it back to editing mode which should not be the way its done, if yesterday it worked just fine when i opened the file. How do i get the All Sheets button to turn back on permentantly?13KViews9likes30CommentsA generalised Lambda helper function that return arrays of arrays using bisection.
Specifically this required days of the week to be sorted by multiple price criteria. [This is intended as a topic for discussion as opposed to a specific request for help] I used the problem to develop a Lambda function that uses a binary tree to select the rows at the leaf nodes and perform a straightforward sort. The results then get stacked pairwise until the blue table of results is returned BYROWλ "Applies a user-defined function by row and collects array results into a stacked array" =LET(resultϑ, BYROW(array, ThunkFnλ), BinaryTreeλ(ROWS(resultϑ), "", Derefϑarrλ)) The main bisection recursion is hidden from the user BinaryTreeλ "Generates binary numbers stacking blocks pairwise to an upper limit" =LET( maxNode, BASE(nodeCount - 1, 2), maxLevel, LEN(maxNode), level, LEN(parentNode), maxChild, LEFT(maxNode, 1 + level), IF( level < maxLevel - 1, LET( childNode0, BinaryTreeλ(nodeCount, parentNode & 0, FNλ), childNode1, BinaryTreeλ(nodeCount, parentNode & 1, FNλ), IF((parentNode & 0) = maxChild, childNode0, VSTACK(childNode0, childNode1)) ), IF( (parentNode & 0) = maxChild, FNλ(parentNode & 0), VSTACK(FNλ(parentNode & 0), FNλ(parentNode & 1)) ) ) ) but it is intended to non-problem specific and hence reusable. A key feature is that the user provides a Lambda function that performs the required calculation as if BYROW would return the result, but the function is converted to one the returns a thunk and hence avoids the array of arrays problem. ThunkFnλ "Accepts a user-defined Lambda function that returns an array result and generates a related function that returns the corresponding thunk" =LAMBDA(arr, LAMBDA(userFnλ(arr))) Finally Derefϑarrλ "Dereferences a term from a thunk array using a zero-based binary pointer and expands the resulting scalar thunk" =INDEX(resultϑ, DECIMAL(ptr, 2) + 1, 1)() converts the binary node pointer to a decimal index. This is all crazily heavy but, hopefully, would be simple to reuse for other problems since the main processing is problem independent.11KViews6likes21CommentsRefreshing stock prices in Excel has stopped working - asking for account with subscription
Hi, My excel with automatically updating share prices stopped working yesterday. I thought it was a blip, but still not working today. I'm getting the following message: 'COULDN'T REFRESH DATA TYPES you need to sign in with an account associated with a subscription to use these data types ' I am signed in to my Office365 account and nothing has changed. Do we now need to subscribe/pay for share data or is there something else?Solved11KViews6likes29CommentsRack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers! REV_2025-10-16: updated function definitions for SCANBYROW and SCANBYCOL to be able to handle TYPE 16 and TYPE 128 values.506Views5likes17CommentsSolving 'The Assignment Problem' with Lambda
The Setup The problem is simple. Given a 'cost matrix', assign tasks to workers to minimize total cost needed to complete the tasks. Workers may not perform more than 1 task. Assignment problem - Wikipedia Methods for Solving The Hungarian algorithm is a very popular method for solving the problem. I don't think this method is transferrable to Excel and would not be capable of generating multiple solutions where there are ties for lowest cost. Hungarian algorithm - Wikipedia I believe another approach to solving the Assignment Problem is to essentialy generate all possible solutions to solving the rook's problem/rook polynomial. Rook polynomial - Wikipedia Excel solution I think my solution takes some inspiration from the rook's problem. My goal was to generate all possible combinations and then take only ones with minimal 'cost'. For this problem I built my solution to accomodate the addition of more 'workers' but locked the tasks at 3. Expanding the tasks beyond 3 is maybe something best handled in part by Python's itertools (A subject for another adventure!). Discussion I welcome any alternative approaches to solving this problem and/or any refinements to my solution. Attached you will find a workbook with the original cost matrix and a larger cost matrix for testing purposes. Happy Holidays! 'Solve =LAMBDA(staff,cost_matrix,LET( r, ROWS(staff), c, COLUMNS(cost_matrix), counter, SEQUENCE(r), GenerateCombin, LAMBDA(a, v, LET( taskA, EXPAND(v, PRODUCT(r - 1, r - 2), , v), filtered, FILTER(counter, counter <> v), taskB, TOCOL(filtered * SEQUENCE(, r - 2, 1, 0)), loop, LAMBDA(acc, val, LET( vector, TOCOL(FILTER(filtered, (filtered <> v) * (filtered <> val))), VSTACK(acc, vector) ) ), taskC, DROP(REDUCE("", filtered, loop), 1), VSTACK(a, HSTACK(taskA, taskB, taskC)) ) ), combin_matrix, DROP(REDUCE("", counter, GenerateCombin), 1), staff_matrix, INDEX(staff, combin_matrix), val_matrix, INDEX(cost_matrix, combin_matrix, SEQUENCE(, c)), totals, MMULT(val_matrix, SEQUENCE(c, , 1, 0)), lowest, MIN(totals), stack, DROP(HSTACK(staff_matrix, val_matrix, totals), , -1), filtered_stack, TOCOL(FILTER(stack, totals = lowest)), wrapped, WRAPROWS(filtered_stack, c), IFERROR(VSTACK(Tasks, wrapped), "-") ))Solved5.4KViews5likes21CommentsExcel's stock data type can't find several stock exchange indexes (i.e: FTSE, CAC 40, Hang Seng...)
I noticed by searching around that this is a topic that has been asked quite a bit with no clear answers that get to bottom of this issue. I'm well aware the Excel data types feature is still a work in progress, but by now the dev team could've done a better job on this one. The data selector search tool for instance couldn't be any worse - it really SUCKS!!! So, this is a list of mainstream stock exchanges that Microsoft claims they are supported according to the link below, but I've tried every possible code/ticker related to them that I could and no sucess at all to have them linked to Excel data types. https://support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6 Index Exchange Country Codes/tickers attempted AEX Euronext Amsterdam Netherlands AEX, XAMS CAC 40 Euronext Paris France CAC 40, CAC40, PX1, XPAR FTSE MIB Borsa Italiana Italy FTMIB, MIB, FTSE MIB, XMIL FTSE 100 London Stock Exchange United Kingdom FTSE, FTSE100, FTSE 100, UKX, XLON Hang Seng HKEX - Hong Kong Exchanges Hong Kong HSI, Hang Seng, XHKG IBEX 35 Bolsa de Madrid Spain IBEX, IBEX35, IBEX 35, BMEX MOEX Russia MOEX - Moscow Exchange Russia MOEX, IMOEX, MISX FTSE JSE SA Top 40 JSE - Johannesburg Stock Exchange South Africa JSE, FTSE JSE, FTSEJSE, JTOPI, XJSE Kospi KRX - Korea Exchange South Korea KS11, KOSPI, KRX, XKRX IPC Grupo BMV Mexico IPC, MXX, XMEX And come on, we're mostly talking about MAJOR exchanges here, and that are supported by this service according to Microsoft's own support page, so there's no excuse that no one can't find them for crying out loud!!!24KViews5likes16CommentsExcel Community: Simplifying Spaces and Labels
Hi all, As you may have noticed already, we as the Excel Team have done a bit of "spring/summer cleaning" for the community. We have received feedback that the number of "spaces" was simply too many at nine, so we have pared things down. Now, there are three community spaces: Excel: this will be the primary place for posting content, as many of the old spaces have been migrated as labels will find a good home here BI and Data Analysis: this will be a good place for posting about tasks and questions that cut across Excel, Power BI, and other topics in this realm Resources and Community: this will increasingly become a place for folks to share sample files and templates with each other Q: What happened to the other spaces that used to exist? A: They have since been rolled up as "Labels", which you can find in the "More Resources" sidebar under "Labels" of any of the three spaces mentioned above. Look for these on the right side: Please let us know if you have any other questions. Thanks for reading!4.3KViews5likes25CommentsExcel Print Preview not matching the actual printed document
Hey there, My Excel print preview and printer seem to be out of sync. I keep my excel worksheets in "Page Layout" mode when I work, I find it best when I plan to print the document in the end. In the last 2 weeks, none of the documents I've printed are coming out the way they appear on the workbook. I have a workbook that should be 23 pages but it prints 26 instead. The rows are extending over into other pages. Please help!!Solved428KViews5likes65Comments